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