Trying a formula that changes the % it multiplies it by depending on the value of the cell

Would you be willing to use a lookup for the multiplier as well as the last value? The multiplier part should be as easy as *VLOOKUP(AC16,lookup_table,2,TRUE) [note the TRUE in the 4th argument -- that is key].

It is very similar to the LOOKUP() function that you are using: https://support.office.com/en-us/art...8-93a18ad188a1

If I understand your LOOKUP() function, it might look like:
=((LOOKUP(2,1/(X3:X34<>""),X3:X34))-X3)*VLOOKUP(AC16,lookup_table,2,TRUE)

You can shorten the LOOKUP a bit.

This...

LOOKUP(1E100,X3:X34)

Will do the same as this...

LOOKUP(2,1/(X3:X34<>""),X3:X34)

You look like you are missing AND() functions where you are testing your conditions AND(AC16>0,AC16<100) for example.

if you are really stuck on using the IF statement instead of the recommendations from Mr Shorty and Tony Valko, then...
unless some of your possible values in AC16 could be less than 0, you can get rid of half of your if statements.
so you could shorten it to =IF(AC16<100..... then IF(AC16<500 then your last statement. Multiple IF then statements quit working after they find the part that becomes true. But if you want the combination of IF(AC16>0,AC16<100) then you need to add the AND part in front of it per Mr Shorty.

Originally Posted by ryankelley318

when this cell is between 0 and 100 i want it to output the formula * 5%
when it is between 100 and 500 i want it to * 10%
and anything higher than 500 i want it to be at 15%

Your intervals are not well defined.

If the value was 100 should the multiplier be 5% or 10%?