CODEWITHSUNDEEP
excelexcel-formula
SidPatel91
SidPatel91

Reputation: 1

Facing difficulty in Excel when creating a logic for moving average of different variables

So, the point is, in my dataset I have to create a variable "Moving Avg. Amt paid per sq. ft." and the formula or the logic I need is to calculate the last five values as per most recent transactions. i.e. most recent sales by date. but this average should only return value in case it matches the same building and same area variable.

This is what my data looks like

Area ID has three categories. Building number has 5 categories. Date is sorted in ascending order. Now my variable moving average should calculate last 5 averages w.r.t date but for the same building in the same area. e.g. there are buildings 1 and 2 in area 102. I need my Mov Avg. variable to calculate using conditions when it matches criteria of building 1 in 102 for past five sales and when it finds building 2 in the building number variable, it should calculate average of last 5 sales of that building in area 102.

So my approach to this issue was (which is flawed at the moment):

I calculate average of amount paid per sq. foot w.r.t area & building based on dates using the formula

=AVERAGEIFS($N$2:$N$6547,$D$2:$D$6547,D14,$C$2:$C$6547,C14,$B$2:$B$6547,B14)

but I cannot make this formula work, to calculate moving average whenever it meets the criteria. I tried the offset the point as well by 5 but the logic is not right and hence its not working and returning #value in the cells. The formula I used to offset the above condition is

=AVERAGEIFS((OFFSET(N13,5,,5)),$D$2:$D$6547,D13,$C$2:$C$6547,C13,$B$2:$B$6547,B13)
(These formulae are used in column Q of my data)

Need a support from the community as I am badly stuck in making this data useful and I am out of any ideas to make this work.

Edit 1: I am not sure how I can attach my excel file here so you may review the dataset. I have uploaded it on a third party site, for which the link is shared below, so you can view the file in detail. https://file.io/hlciAHJOHzWA

Expected result is as I have mentioned the instruction said "Create a variable called "mov. avg amt. paid per sq ft". For each row, this variable should calculate average amt paid per sq ft for the most recent past five sales (by date) for the same building in the same area."

And my approach to build a logic or formula to make this variable calculate moving average w.r.t date for same building in the same area doesn't seem to work because there might be some flaws.

Upvotes: 0

Views: 74

Answers (1)

P.b
P.b

Reputation: 11588

In Office 365 you could use:

=LET(f,FILTER($N$1:N13,($B$1:B13=B14)*($C$1:C13=C14),""),
     c,COUNTA(f),
     s,SEQUENCE(5,,c-5),
IFERROR(IF(c<5,SUM(f)/c,SUM(INDEX(f,s))/5),""))

If there's less than 5 matches prior to the current sales it'll calculate the average of the count. If 5 or more matches it'll calculate the average of the last 5 prior to the current sale.

Upvotes: 1

Related Questions