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

[iscaspar]

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!

[Sam Capricci]

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

[Sam Capricci]

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.

[iscaspar]

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

Here is a fix:

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

[Sam Capricci]

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})

[iscaspar]

It is giving me an error:Attachment 634053

[Sam Capricci]

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})

[iscaspar]

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

[Sam Capricci]

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.

[iscaspar]

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.

Solved! Thank you. I had the punctuation(?) wrong in my formula!

[Sam Capricci]

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