Need to calculate certain cells based on what one cell shows

**lorne17** · 05-03-2011, 08:57 AM

Hello there,

I am trying to figure out a formula to calculate a percentage to see my profits.

Basically I have prices for products to the left. My qty to the right and my profits to the far right. I have prices for quantities as 24-49, 50-99, 100-199, 200-499. I currently have the formula in profits to read like so: =(('PRICE' * 'PERCENTAGE') - PRICE) * 'QUANTITY' Right now I have that working. However if the quantity goes from 35 to 80, then the price of the product changes and I want that to reflect in my profits cell without having to point it to the correct cell for the "PRICE".

I assume it's merely an if, then statement. So if 'QUANTITY' is 24-49 then it selects the price in that column. If the quantity is changed, then it will find and update the price under the correlating column.

Does that make sense? I have attached my excel for you to view.

Thanks so much,
Lorne

**solnajeff** · 05-03-2011, 09:09 AM

Hi

For the price, if you use one line - 2 and price in A4-A7, then substitute the formula

Please Login or Register  to view this content.

Regards

Jeff

**NBVC** · 05-03-2011, 09:12 AM

Try:

=((LOOKUP(E2,LEFT($A$1:$D$1,FIND("-",$A$1:$D$1)-1)+0,A2:D2)*$C$14)-LOOKUP(E2,LEFT($A$1:$D$1,FIND("-",$A$1:$D$1)-1)+0,A2:D2))*E2

copied down

**NBVC** · 05-03-2011, 09:16 AM

I think solnajeff meant?

IF(E2<50,A2,IF(E2<100,B2,IF(E2<200,C2,D2)))

and inserted in your formula would be:

=((IF(E2<50,A2,IF(E2<100,B2,IF(E2<200,C2,D2)))*$C$14)-IF(E2<50,A2,IF(E2<100,B2,IF(E2<200,C2,D2))))*E2