Stock Rotation Formula Help Needed

[Matador56]

Hi!

I am doing a stock rotation report for my Company which basically displays an Age Profile (How long since stock was created) of both Stock on the Ground and Stock Despatched in the past day.

I would like to do a formula beside this table (Will attach image showing example) that returns the amount of product that could have been sent that is older than the product that was actually sent. I have tried a few times, but just cannot seem to get the logic to work how I want it to work.

Happy to provide further information if you need it.

Thank you if anyone is able to assist

 

[KRice]

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2		Age Profile of Current Stock				Age Profile of Dispatched Stock
3	Product	2+ y	1-2 y	6-12 mo	<6 mo	2+ y	1-2 y	6-12 mo	<6 mo		Qty older than recently dispatched
4	A		18		96				2		2
5	B				26				15		0
6	C				2				13		0
7	D	15	21	26				150			36
8	E				70				4		0
9	F			143	7		14		2		2
10	G			16	90		28				0
11	H		9	14	106				5		5
Sheet3

Cell Formulas
Range	Formula
K4:K11	K4	=(SUM(B4:D4)>=I4)*I4 + (SUM(B4:D4)<I4)*SUM(B4:D4) + (SUM(B4:C4)>=H4)*H4 + (SUM(B4:C4)<H4)*SUM(B4:C4) + (B4>=G4)*G4 + (B4<G4)*B4

 

[Matador56]

Thanks Kirk! Works exactly as intended. Much simpler than anything I would have ever come up with to solve.

 

[KRice]

You're welcome...that's good to hear.