How to put a cap on an amount?

[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. Valko]

Try this:

=MIN(500,IF(B5<0.01,0,B5*LOOKUP(B5,{0;6001},{0.012;0.009})+18))

 

[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

 

[Ross]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[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. 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)))

 

[Ross]

That works great! Thanks again.

Have a good week...

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

That works great! Thanks again.

Have a good week...