Formula to calculate new cell value based on a range of possible increases

Hello,

I am sorry if my title is terrible, but here is what I am looking to do:

I have an Excel with SKUs in one collum and prices in another, ~4000 rows total.
I am looking to calculate new prices based on the following rules:

If equal to or under $20, then add $2
If over $20, but equal to or under $100, then add $5
If over $100, but equal to or under $200, then add $10
If over $200, but equal to or under $300, then add $15
If over $300, then add $20

Thanks a bunch in advance!

you have under 20 and over 20 etc, what about exactly 20 and exactly 100 etc.?

I would try something like this... =A2+LOOKUP(A2,{0,20,100,200,300},{2,5,10,15,20})
but key breakpoints are important so at 20 it will jump to 5 instead of staying at 2, so you may need to change the first distribution points to 0,19,99,199,299 or maybe 0,21,101,201,301 or something like that. You may have to experiment with it.

then give the formula posted in post #3 a try with this adjustment to see if it works for you...
=A2+LOOKUP(A2,{0,21,101,201,301},{2,5,10,15,20})

I cannot open your attachment, did you click go advanced (below this quick reply window) then manage attachments, then browse, upload, close and ok to upload?
offhand the only reason I can think of an error is if you are going against text that looks like numbers instead of numbers. If you use =ISTEXT(A2) and it returns TRUE then you are going against text instead of numbers.
if you are running up against text then you can alter the formula with this instead ... =A2+LOOKUP(A2*1,{0,21,101,201,301},{2,5,10,15,20})

Originally Posted by Sambo kid

I cannot open your attachment, did you click go advanced (below this quick reply window) then manage attachments, then browse, upload, close and ok to upload?
offhand the only reason I can think of an error is if you are going against text that looks like numbers instead of numbers. If you use =ISTEXT(A2) and it returns TRUE then you are going against text instead of numbers.
if you are running up against text then you can alter the formula with this instead ... =A2+LOOKUP(A2*1,{0,21,101,201,301},{2,5,10,15,20})

I did this time. Also changed it to numbers, and =ISTEXT(B2) returns as false

Not sure what you've got because it is difficult to diagnose a problem from a snapshot so I am attaching an example of mine for you to see.

Great, glad that worked for you, AND thank you for the rep!