How to put a cap on an amount?

R

Ross

Hi Everyone

The formula that I already have is:
=IF(B5<0.01,0,IF(B5<6001,(B5*0.012)+18,IF(B5>6000,(B5*0.009)+18,0))) I need
to add something to cap the result at 500. Can someone help?

Thanks...
 
T

T. Valko

Why MIN and LOOKUP?

LOOKUP is doing the exact same thing as the nested IFs but is a few
keystrokes shorter. Adding the +18 to every result of the nested IFs is
redundant since you just need to add it once.

You wanted to cap the result at 500.

Suppose B5 contained 65000.

65000 is >6000 so:

65000*0.009+18 = 603

So we use MIN to cap the result at 500:

=MIN(500,603) = 500

If B5 contained 50000:

50000 is >6000 so:

50000*0.009+18 = 468

=MIN(500,468) = 468
 
R

Ross

Thanks! That was a very good explanation. Hopefully, I'll be able to work
other solutions with it in the future.
 
R

Ross

Hi again, Biff

Sorry to bother again so soon, but I was wondering if you could tell me how
to exclude the $18 from just the "6,000 or less" part, as it seems I
misunderstood the requirements?

Thanks again
 
T

T. Valko

Ok, let's go back to the nested IFs:

=MIN(500,IF(B5<0.01,0,IF(B5<=6000,B5*0.012,B5*0.009+18)))
 
Top