If / And / Or Formula

[markmogli]

Hello, I'm after some expert help:

My spreadsheet has some sales forecast / actual data in it. I sell products with a fluctuating price based on demand.

A1 is is the product.

B1 is the 1st price I put a product on sale for.

F1 is the gross profit margin formula.

After a few weeks on sale, the price may be adjusted to reflect demand, either up or down.

C1 is the second price the product is priced at. I now need F1 to update the GP on the new selling price.

I may change the price a 2nd time, entering the new price into D1. I now need F1 to update the GP based on the new selling price.

I may change the price a 3rd and final time, entering the new price into E1, I now need F1 to update the GP on the new selling price.

It's driving me bat $%^& crazy - I've tried various IF / AND / AND/OR and I'm getting bogged down in nestled IF's which may be the wrong route....?

Argh!

 

[Joe4]

What is the current formula in cell F1, just based on the B1 entry?

 

[markmogli]

What is the current formula in cell F1, just based on the B1 entry?

It's just a GP formula from another sheet. It just needs to look at the newest input from B1, C1 or D1

 

[Joe4]

You can nest the IF portion, i.e.
=IF(E1<>"",use E1 in formula,IF(D1<>"",use D1 in formula,IF(C1<>"",use C1 in formula,use B1 in formula)))

There might be a way to simplify it, but without seeing your exact formula, I cannot say that for sure.

 

[markmogli]

You can nest the IF portion, i.e.
=IF(E1<>"",use E1 in formula,IF(D1<>"",use D1 in formula,IF(C1<>"",use C1 in formula,use B1 in formula)))

There might be a way to simplify it, but without seeing your exact formula, I cannot say that for sure.

The formula in F1 is just (B1-Z1)/B1 (where Z1 is the item cost and B1 is the revenue. It's just the revenue may change to that of data in C1 or indeed D1.

 

[Joe4]

OK, then just substitute into my format:
=IF(E1<>"",(E1-Z1)/E1,IF(D1<>"",(D1-Z1)/D1,IF(C1<>"",(C1-Z1)/C1,(B1-Z1)/B1)))

 

[markmogli]

OK, then just substitute into my format:
=IF(E1<>"",(E1-Z1)/E1,IF(D1<>"",(D1-Z1)/D1,IF(C1<>"",(C1-Z1)/C1,(B1-Z1)/B1)))

Hi Joe,

That formula does indeed work, however; it only works when data is entered in to one cell.

I would like to leave the data entered in to cells E1, D1 & C1 so I can see the price changing over time.

How can this formula be adapted to allow this?

Thanks,

 

[Joe4]

I would like to leave the data entered in to cells E1, D1 & C1 so I can see the price changing over time.

How can this formula be adapted to allow this?

I am afraid I don't follow you. Can you work us through an actual example?

Note that a formula can only return one single value at a time. If you want to see multiple values, you will want to use multiple formulas in multiple cells.
If that is the case, then all you really need is a calculation for each cell, i.e.
=(B1-Z1)/B1 in one cell,
=(C1-Z1)/C1 in another cell,
etc.

 

[markmogli]

I am afraid I don't follow you. Can you work us through an actual example?

Note that a formula can only return one single value at a time. If you want to see multiple values, you will want to use multiple formulas in multiple cells.
If that is the case, then all you really need is a calculation for each cell, i.e.
=(B1-Z1)/B1 in one cell,
=(C1-Z1)/C1 in another cell,
etc.

Book1.xlsx
	B	C	D	E	F	G
2			30-Nov	01-Dec	02-Dec
3		Cost	Price 1	Price 2	Price 3	GP
4	Item 1	£1.00	£10.00			90%
5	Item 2	£2.00	£5.00	£10.00		80%
6	Item 3	£3.00	£13.00	£8.00	£5.00	40%
Sheet1

Cell Formulas
Range	Formula
G4	G4	=(D4-C4)/D4
G5	G5	=(E5-C5)/E5
G6	G6	=(F6-C6)/F6

Hopefully this helps. The formulas in column G need to look at column D first and give me a GP. When I decide to change the price, as on "item 2", the GP will change based on the new selling price, so the formula now needs to look in E5 instead of D5.

 

[Joe4]

Oh, you are putting them on different rows.
You just need to take the original formula I gave you, and copy the formula down for all rows.

So put this formula in cell G4:
=IF(E4<>"",(E4-Z4)/E4,IF(D4<>"",(D4-Z4)/D4,IF(C4<>"",(C4-Z4)/C4,(B4-Z4)/B4)))
and copy down to row G6.