# Inventory Price Calculation

1. ## Inventory Price Calculation

I am doing a price increase calculation comparisons and using scenario manger.
I have written the formula below. This works until it hits a value greater than 800.00. (Attachment has example)

=IF(\$B882<=50,(\$B882*H\$3),IF(AND(\$B882>50,\$B882<=100),(\$B882*I\$3),IF(AND(\$B882>=101,\$B882<=200),(\$B882*J\$3),IF(AND(\$B882>=201,\$B882<=300),(\$B882*K\$3),IF(AND(\$B882>=301,\$B882<=500),(\$B882*L\$3),IF(AND(\$B882>=501,\$B882<=1000)*(\$B882*M\$3),IF(AND(\$B882>=1001,\$B882<=2000)*(\$B882*N\$3),IF(AND(\$B882>=2001,\$B882<=3000)*(\$B882*O\$3),IF(AND(\$B882>=3001),\$B882*P\$3)))))))))

I can not seem to find the problem ..... would appreciate any assistance.   Register To Reply

2. ## Re: Inventory Price Calculation

Try ``Please Login or Register  to view this content.``  Register To Reply

3. ## Re: Inventory Price Calculation

This formula worked !! Why did the IF(AND() not work above 800.00?? Were the IF(AND()'s not necessary???

How do I list this as solved?? Poor memory   Register To Reply

4. ## Re: Inventory Price Calculation

=IF(\$B882<=50,(\$B882*H\$3),IF(AND(\$B882>50,\$B882<=100),(\$B882*I\$3),IF(AND(\$B882>=101,\$B882<=200),(\$B882*J\$3),IF(AND(\$B882>=201,\$B882<=300),(\$B882*K\$3),IF(AND(\$B882>=301,\$B882<=500),(\$B882*L\$3),IF(AND(\$B882>=501,\$B882<=1000)*(\$B882*M\$3),IF(AND(\$B882>=1001,\$B882<=2000)*(\$B882*N\$3),IF(AND(\$B882>=2001,\$B882<=3000)*(\$B882*O\$3),IF(AND(\$B882>=3001),\$B882*P\$3)))))))))

The above had "*" rather than ","

The ANDs are not necessary: look at the IF statements and you will see (work out!) that the AND is implied.

e.g Value of 150 is not less than 50, not less 100 BUT is less than 200 i.e lies between 101 and 200.  Register To Reply

5. ## Re: Inventory Price Calculation

This is how I would solve it:
Formula:  `Please Login or Register  to view this content.`

As you can see I changed the first couple of values for the purpose of testing and I'm too lazy to fix it back.   Register To Reply

6. ## Re: Inventory Price Calculation

Why did I not do like this instead? My brain is full of glitches and ****...
Formula:  `Please Login or Register  to view this content.`  Register To Reply

7. ## Re: Inventory Price Calculation

Thanks for the help and the explanation!!!  Register To Reply