If statements for pricing updates

[mack zilla]

Well, I thought I had this one but I have been stumped. I am creating formulas (haven't even began working with the actual file) that will update pricing.

I want it to:
1. Look at the value I have entered
2. Determine if it's between two cells I say to look at
3. Than populate the amount of change to be added to the product price.

For instance:
If I have a product under $9I add $.09 to the sale price making it a total of $9.09. Now I got this to work but my new pricing update states something much more complicated. If the item is between $10-$50 it's dependant upon the cents amount too. If it's between 10-50 and the cents is below .29 round up to 29. If between .29 and .49 round to .49: repeat. [00-.29=.29], [.29-.49=.49], [.49-.79=.79], [.79-.89=.89], [.89-.99=.99].

I attached my sheet to show how I was doing it previously cents.xlsx

[JBeaucaire]

Maybe:

=IF(INT(A2)<50, INT(A2) + LOOKUP(MOD(A2,1), {0,0.3,0.5,0.8,0.9}, {0.29,0.49,0.79,0.89,0.99}), SUM(E2+A2))

[mack zilla]

No luck with that one but I am tinkering with it now. The only problem is that it is pulling a number no matter what's entered in cell a2 and populating. I need it to only populate under the condition it is between 10-50 dollars.

[mack zilla]

But your work on the rounding was perfect now if I can just figure out how to unite the two.

[JBeaucaire]

Maybe:

IF(AND(INT(A2)<50,INT(A2)>3), INT(A2) + LOOKUP(MOD(A2,1), {0,0.3,0.5,0.8,0.9}, {0.29,0.49,0.79,0.89,0.99}), SUM(E2+A2))

[mack zilla]

Close, I don't think I am being clear enough because you've nailed the rounding part. This formula needs to work in conjunction with my other formula. If it see's that the value falls between 10-50 dollars it should use the rationale of your formula. If not, it should choose the pricing/cents in my formula.

[mack zilla]

This worked: =IF(AND(A2>=$B$2,A2<=$C$2),0.09,IF(AND(INT(A2)<50,INT(A2)>3), INT(A2) + LOOKUP(MOD(A2,1), {0,0.3,0.5,0.8,0.9}, {0.29,0.49,0.79,0.89,0.99})-A2,IF(AND(A2>=$B$4,A2<=$C$4),0.99,IF(AND(A2>=$B$5,A2,$C$5),9.99,0))))

Thanks J your help is what made it possible.

[JBeaucaire]

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.