# 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.

Try

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

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.

5. ## Re: Inventory Price Calculation

This is how I would solve it:
Formula:

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.

6. ## Re: Inventory Price Calculation

Why did I not do like this instead? My brain is full of glitches and ****...
Formula:

7. ## Re: Inventory Price Calculation

Thanks for the help and the explanation!!!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1