Year over year counts

[maribelbc]

Hello,

I'm tracking inventory movement year over year to get ageing. I have the qty movement annually summed (purchase, sales, adj, etc.). I need to age in <1yr, 1-3yrs, and >3years groups. I'm using Ifs and sum but when the sum results in negative, I still need the positive value within the range to show (that's a purchase within that year range = age) and that value to deduct from the positive qty from the year range before.

	A	D	C	D	E	F	G	H
1
2	2016	2017	2018	2019	2020	2021	2022	2023
3	850​	-437​	-200​	-16​	1​	-2​	-100​	850​

So for above sample, the result should be:

<1Year (2023)	1-3Years (2020-2022)	>3Years (2016-2019)
850	1	95

Please assist. Thank you.

 

[RobP]

Hi,

I can't see your formula, but if you use =ABS(number) - it will always give you a positive result ? Perhaps you can integrate it into what you have already ?

cheers
Rob

 

[maribelbc]

Hi,

I can't see your formula, but if you use =ABS(number) - it will always give you a positive result ? Perhaps you can integrate it into what you have already ?

cheers
Rob

Thanks, Rob.

I've never used that and not sure how to integrate. Below is another sample.

2014	2015	2016	2017	2018	2019	2020	2021	2022	2023
314​	40​	-18​	-167​	320​	-264​	51​	31​	344​	-152​

<1 Year =IF(AC10<0,"",IF(AC10>0,AC10,""))	1-3 Years =IF(SUM(Z10:AB10)<0,"",IF(SUM(Z10:AB10)>0,SUM(Z10:AB10),""))	Over 3 Years =IF(SUM(T10:Y10)<0,"",IF(SUM(T10:AB10)>0,SUM(T10:AB10)-SUM(Z10:AB10),""))
	426	225

This should be:

<1 Year	1-3 Years	Over 3 Years
	426​	73​

So I think i'm going about this all wrong lol. The -152 would be deducted from the over 3 year count, and if the -qty in 2023 is greater than the over 3 years, it should take away from the 1-3 years.

 

[RobP]

Where you have Sum(z10:ab10) for example try ABS(Sum(z10:ab10)) to give you an always positive result.

Rob