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!

 

[markmogli]

Hey Joe,

Your proposed formula still relies on data being removed from one of the 3 price cells to work.

I have a convoluted formula in my head and would like a shortened, easy version of this formula to go in column G: if E4 and F4 are empty, use D4 for the margin formula, if F4 is empty and D4 contains data, look in E4, if D4 and E4 contain data, use F4.

The margin is calculated based on the fixed cost of the item and sale price, I'd like a dynamic way to look at the changing price.

 

[Joe4]

Your proposed formula still relies on data being removed from one of the 3 price cells to work.

No, it doesn't.
Is the issue possibly that your cells are not really blank, but contain something like a single space in them?

Can you provide an example to show exactly in what situation it does NOT work?

 

[markmogli]

No, it doesn't.
Is the issue possibly that your cells are not really blank, but contain something like a single space in them?

Can you provide an example to show exactly in what situation it does NOT work?

Here is where I have manually told the formulas in column G to look. Cells E4, E5 & F5 are completely empty.

Book2.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 1	£1.00	£10.00	£12.00		92%
6	Item 1	£1.00	£10.00	£12.00	£15.00	93%
Sheet1

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

I must be doing something wrong with the formula?

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 1	£1.00	£10.00	£12.00		90%
6	Item 1	£1.00	£10.00	£12.00	£15.00	90%
Sheet3

Cell Formulas
Range	Formula
G4:G6	G4	=IF(D4<>"",(D4-C4)/D4,IF(E4<>"",(E4-C4)/E4,(F4-C4)/F4))

 

[Joe4]

OK, it looks like you changed some of your column references around (from your original question), and did not adjust the formula properly.
This should do what you want, based on your formula.
Place in cell G4 and copy formula down to cells G5 and G6.

=IF(F4<>"",(F4-C4)/F4,IF(E4<>"",(E4-C4)/E4,(D4-C4)/D4))

 

[markmogli]

OK, it looks like you changed some of your column references around (from your original question), and did not adjust the formula properly.
This should do what you want, based on your formula.
Place in cell G4 and copy formula down to cells G5 and G6.

=IF(F4<>"",(F4-C4)/F4,IF(E4<>"",(E4-C4)/E4,(D4-C4)/D4))

Ahhhh! Yes, I see, it's looking at the last price first and then working backwards to the first price.

Winner!

Ta Joe, big hugs and kisses xx

 

[Joe4]

You are welcome.