Calculating required quantities dependent on date

[ozbeachbum]

dy calculation.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M
2	Quarters	Percentage	O Qty	Date	DV	UV	Date	Qty	Date	Qty	Date	Qty
3
4	1st Quarter:	0.20%	1000	22/07/1999	$ 40	$ 20	12/07/1999	100	11/07/1999	50	10/07/1999	25
5	2nd Quarter:
6	3rd Quarter:
7	4th Quarter:
Sheet1

Cell Formulas
Range	Formula
C4	C4	=IF(E4>H4,(F4/(G4*D4)),IF(E4<H4,((F4/(G4*(D4+I4)))),0))

Hi all,

C4 needs to show the result: IF(E4>H4&orJ4&orL4,(F4/(D4 [+quantities in I4 & K4 where dates in H4 & J4 are less than E4][-quantity in M when date in L4 is less than E4] *G4))

It becomes more complex in the second quarter as the formula will need to also calculate any quantities where the dates on row 4 are greater then the date in E4,

along with calculation for row 5 same as row 4

I have tried numerous formulas, but can't get past using only 2 dates as shown in C4

Any assistance would be greatly appreciated.

Cheers,

Dave.

 

[sijpie]

For your cell C4 the formula is

=F4/((IF(H4<E4,I4,0)+IF(J4<E4,K4,0)-IF(L4<H4,M4,0))*G4)

You see: it is easier to first add (and substract) each of the quantities based on the dates, and only then do the multiplication and division.

You could also have added an extra cell at the end (N4) called Qty Change with the formula

=IF(H4<E4,I4,0)+IF(J4<E4,K4,0)-IF(L4<H4,M4,0)

and then refer to that in C4 with

=F4/(N4)*G4)

I don't understand your comment about Q2 calculation. Try to explain it in another way

 

[ozbeachbum]

For your cell C4 the formula is

=F4/((IF(H4<E4,I4,0)+IF(J4<E4,K4,0)-IF(L4<H4,M4,0))*G4)

You see: it is easier to first add (and substract) each of the quantities based on the dates, and only then do the multiplication and division.

You could also have added an extra cell at the end (N4) called Qty Change with the formula

=IF(H4<E4,I4,0)+IF(J4<E4,K4,0)-IF(L4<H4,M4,0)

and then refer to that in C4 with

=F4/(N4)*G4)

I don't understand your comment about Q2 calculation. Try to explain it in another way

Thank you that is very helpful.
Q2 I can work this out based on what you have given me.
Thanks again.
Dave.