IF Statement

[couevans]

Hello everyone,

I was hoping if someone could help me out with an IF Statement that will tell me where I should apply my additional cost, either to shipping price or retail price. For instance, I have an additional cost of $0.99 that I would like to add to either the shipping price or my retail price. What I am trying to achieve here is if the retail price is anywhere from $0.01 - $8.99, I want the $0.99 added to the retail price. If the item has a retail price of something like $9.99 or $29.99, then I want to add the $0.99 to the shipping price.

Any help would be greatly appreciated.

[leelnich]

Hi couevans- The attached workbook demonstrates how you could implement your If statements.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[Sam Capricci]

something like this??? =IF(A2<9,0.99&" retail price",0.99&" shipping price")

[couevans]

That is exactly what I needed. Thank you. However, what if i want to hit the values that are between $10.01 - $18.99 and so forth that aren't $9.99, $19.99, etc.?

[Sam Capricci]

Whose post are you addressing?

[couevans]

Sorry about that. I was addressing yours. @Sambokid

[Sam Capricci]

I'm not sure of your question then. My formula will tag every value less than $9.00 as "0.99 retail price" and every value above 8.99 as "0.99 shipping price". That is what I took from your original posted question.

[couevans]

Sorry for the confusion. Any value that is between 0.01 - 8.99, then yes, it will need 0.99 added to the retail price. However, the values in between 10.00 - 18.99 will also need the 0.99 added to the retail price. Same goes for 20.00 - 28.99 and so forth.

As for values that have 9.99, 19.99, 29.99, etc., they will need the 0.99 added to the shipping price.

Does this make sense? I am working with items that have a wide range of retail prices - not just 9.99 and under. Thanks for your help on this, it's greatly appreciated.

[avk]

Dear Conuevans, if retail price 8.99 than add 0.01 in retail price &
if retail price 8.01 than added 0.99 in retail price & same applicable for shipping price.
or
if retail price 8.99 than add 0.01 in shipping price
if retail price 8.01 than added 0.99 in shipping price
kindly give output result (enter manually) with all aspects.

[Sam Capricci]

how far do these increments go?

[couevans]

Sambo kid, they go up to 119.99.

[Sam Capricci]

given your parameters I would probably build a table and use a vlookup. See the attached as an example.

[leelnich]

If the .99 cost is in B1, Retail price is in cell B2, and Shipping price is in B3:
This formula shows the adjusted shipping price for ANY x9.99 trigger amounts {9.99,19.99,29.99,...}, otherwise leaves it unchanged:

=IF(MOD($B$2,10)=9.99,$B$3+$B$1,$B$3)

The corresponding formula adjusts the retail price for any amounts EXCEPT the x9.99 triggers:

=IF(MOD($B$2,10)=9.99,$B$2,$B$2+$B$1)


Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[couevans]

You are a life saver, Sambo kid! Thank you so much for your help.

[Sam Capricci]

You're welcome and thank you for the rep!