Dec 03 2023 02:41 AM
Dear Excel Community,
I'm hoping someone can assist with my conundrum !
Please see attached workbook. In the spreadsheet, i'm looking at data between two periods, 2022/23 vs 2023/24. The data is broken down by month.
Because we are still working through 2023/24, the YTD figures are not comparable. What i would like to create is a dynamic formula that adds up all the data entered for 2023/24 and then compares to the same period for the previous year. As more data gets added to another month in 2023/24, the total reflects this and then selects the corisponding period for the previous year.
Hope the above makes sense ?
I would like to have the total display in the "Period comparison" column.
As always, thanking you all for your help to date and i'm hopeful someone can assist !
Thanks
Raj
Dec 03 2023 03:23 AM
Dec 03 2023 11:20 PM
Thank you for this suggestion. It kind of does the trick as i assume it's adding up based on the current month -1.
Is there anyway to modify this formula so that it only adds up cells based on data entered some where else ? i.e matching the months containing data in line 23 ? eg line 23 only contains data up till October so the calculation for P18 should be based on how many months data is in line 23 ?
Hope that makes sense ?
If not, i will go with your suggestion.
Thanks Raj
Dec 04 2023 12:32 AM
**
@Mr_Raj_C wrote:Thank you for this suggestion. It kind of does the trick as i assume it's adding up based on the current month -1.
Correct.
@Mr_Raj_C wrote:Is there anyway to modify this formula so that it only adds up cells based on data entered some where else ? i.e matching the months containing data in line 23 ? eg line 23 only contains data up till October so the calculation for P18 should be based on how many months data is in line 23 ?
Row 23 contains data for 12 months. 0 is also considered as data, as it is always possible to have months with zero sales (or whatever). Think about the COVID pandemic or a natural disaster.
Dec 04 2023 12:57 AM
Dec 04 2023 03:33 AM
If you remove the 0 in row 23 then you would remove a formula. Is that really what you want?
Dec 04 2023 04:15 AM
SolutionA couple of 365-specific ideas. The first is to take a 22/23 value into account only if the corresponding 23/24 cell contains a number:
= SUM(FILTER(food23, ISNUMBER(food24)))
The second is to accumulate a running total up to but not including the first blank cell
= LET(
ADDλ, LAMBDA(a,f, a+f),
accumulated23, SCAN(0, food23, ADDλ),
XLOOKUP(FALSE, ISNUMBER(food24), accumulated23 - food23)
)
I prefer the first though.
Dec 04 2023 06:19 AM
@Peter Bartholomew Thank you for the suggestion and i believe the first example you have mentioned should do the trick !
I'm trying to implement it into my spreadsheet but with no luck. I'm sure I'm missing a few steps ? Seems i need to define a list perhaps ?
I'm afraid my Excel knowledge is not comparable to your level :)
Thanks Raj
Dec 04 2023 06:20 AM
Dec 04 2023 11:03 AM
As variant
=LET(
n, XMATCH(TRUE,C23:N23>0,0,-1),
YoY, SUM(TAKE(C23:N23,,n))/SUM(TAKE(C20:N20,,n))-1,
IFERROR(YoY, 0) )
Dec 04 2023 04:02 PM
The things that could go wrong are to try to use the formula in a legacy version of Excel; FILTER only exists in the dynamic array versions of Excel (365 and 2021). Also the defined names 'food23' and 'food24' must be correctly specified (using Name Manager or the AFE).
food23
='Waste Board'!$C$33:$N$33
food24
='Waste Board'!$C$34:$N$34
You could simply use the range references directly but having once described the A1 notation as 'an abomination that has no place in any computing environment' I am slightly reluctant to work without defined names!
Dec 05 2023 12:03 AM
@Peter Bartholomew Thank you. I will have a play around and try and get it to work.
We are definitely using Office 365 at work, so the the FILTER function should work fine.
Dec 05 2023 12:04 AM
@SergeiBaklan Thank you for the alternative option. i will give it a try.
Dec 05 2023 06:36 AM
A nice thing about your XMATCH/TAKE combination is that it returns a range reference so, in other circumstances, one could still use SUMIFS etc. to refine the calculation further.
Dec 06 2023 03:07 AM
@Peter Bartholomew Oh, yes
Dec 04 2023 04:15 AM
SolutionA couple of 365-specific ideas. The first is to take a 22/23 value into account only if the corresponding 23/24 cell contains a number:
= SUM(FILTER(food23, ISNUMBER(food24)))
The second is to accumulate a running total up to but not including the first blank cell
= LET(
ADDλ, LAMBDA(a,f, a+f),
accumulated23, SCAN(0, food23, ADDλ),
XLOOKUP(FALSE, ISNUMBER(food24), accumulated23 - food23)
)
I prefer the first though.