Multiple IF and AND statement

[Woofy_McWoof_Woof]

Hi, I'm attempting to create an IF statement that will depend on three potential outcomes and to also split the outcomes into two columns. For instance in the attached picture in row 3 there is a positive position on day one of 5, on day 2 this changes to positive 10, a change of positive 5. Therefore, the change of positive 5 will appear on the statement and 0 will appear on the invoice. The same logic applies to to row 4 even though the position on day 2 is less than day one (but as the position on day 2 is still above zero) so it appears on the statement.

However, in row 5 the position on day one is positive 5 though the position on day 2 is negative 10, a change of negative 15. I need negative 5 to appear on the statement and negative 10 to appear on the invoice.

This logic should apply to all rows and split the volume out accordingly, I have tried to do a few different combinations on IF and AND statements and I can get the logic to work when its either all on the invoice or all on the statement but can't seem to get it to work when the volume should be split across both the statement and invoice.

Any help would be greatly appreciated.

Cheers

 

[jasonb75]

Is this what you need?

Book1
	B	C	D	E	F	G	H	I
3	5		10		5		5	0
4	5		2		-3		-3	0
5	5		-10		-15		-5	-10
6	-10		15		25		15	10
7	-10		-15		-5		0	-5
8	-10		-5		5		0	5
Sheet4

Cell Formulas
Range	Formula
H3:H8	H3	=IF(AND(B3>0,D3>0),F3,IF(AND(B3<0,D3<0),0,MAX(B3+D3,D3)))
I3:I8	I3	=F3-H3
F3:F8	F3	=0-B3+D3

 

[Snakehips]

Here is my effort.
Not sure if this is a universal solution.
I can only assume logic that appears to solve for the given data.

Book1
	B	C	D	E	F	G	H
1	Day1	Day1	Day2	Day2	Change	Statement	Invoice
2	5	Positive	10	Positive	Statement	5	0
3	5	Positive	2	Positive	Statement	-3	0
4	5	Positive	-10	Negative	Both	-5	-10
5	-10	Negative	15	Positive	Both	15	10
6	-10	Negative	-15	Negative	Invoice	0	-5
7	-10	Negative	-5	Negative	Invoice	0	5
Sheet1

Cell Formulas
Range	Formula
G2:G7	G2	=IF(SIGN(B2)<>SIGN(D2),SIGN(D2)*MAX(B2,D2),IF(SIGN(D2)=1,D2-B2,0))
H2:H7	H2	=D2-B2-O2

Hope that helps.

 

[Snakehips]

Sorry.... there was a retained reference in the H formula, above, to a redundant trial column.

My solution should be......

Book1
	B	C	D	E	F	G	H
1	Day1	Day1	Day2	Day2	Change	Statement	Invoice
2	5	Positive	10	Positive	Statement	5	0
3	5	Positive	2	Positive	Statement	-3	0
4	5	Positive	-10	Negative	Both	-5	-10
5	-10	Negative	15	Positive	Both	15	10
6	-10	Negative	-15	Negative	Invoice	0	-5
7	-10	Negative	-5	Negative	Invoice	0	5
Sheet1

Cell Formulas
Range	Formula
G2:G7	G2	=IF(SIGN(B2)<>SIGN(D2),SIGN(D2)*MAX(B2,D2),IF(SIGN(D2)=1,D2-B2,0))
H2:H7	H2	=D2-B2-G2

 

[Woofy_McWoof_Woof]

Afternoon chaps, sorry for the delay in responding. This works an absolute treat, thank you very much