That works great! Thanks again.

Have a good week...
--
smither fan


"T. Valko" wrote:

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

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

--
Biff
Microsoft Excel MVP


"Ross" wrote in message
...
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
--
smither fan


"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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

--
smither fan


"T. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"Ross" wrote in message
...
Thanks, Biff. As has happened several times in the past, you have
provided
the perfect solution! Could I prevail on you to explain it for me?
Why
MIN
and LOOKUP?

Have a good weekend.
--
smither fan


"T. Valko" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP


"Ross" wrote in message
...
Hi Everyone

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

Thanks...
--
smither fan