Dynamic Average last 3 months sales use OFFSET and COUNTA

[leyan]

I have the following formula to calculate the last 3 months' average sales, but it only returns a value of 0. I suspect the issue might be related to COUNTA(2:2), but I'm not sure how to fix it. When I run 'Evaluate Formula,' I can see the correct answer at the second-to-last step, but then it becomes 0. Why is this happening? (I added a + sign at the beginning so that the evaluate steps display the result.)

=+AVERAGE(OFFSET(A2,0,COUNTA(2:2)-3,1,3))

I have tried the following code, but it only works in Google Sheets, not in Excel. (Not exactly dynamic as I have to change the R2 to the new column every month)

=AVERAGE(OFFSET(A2,0,COUNTA(2:2)-3):R2)

Below is the Google sheet link

Untitled spreadsheet - Google Drive

docs.google.com

 

[AlanY]

can try this instead,

=AVERAGE(INDEX(2:2,1,MATCH(9E+307,2:2)-2):INDEX(2:2,1,MATCH(9E+307,2:2)))

 

[Anonymous1378]

What version of excel are you using?

 

[Georgiboy]

If you have the AVERAGEIF formula then maybe:

=AVERAGEIF(G2:X2,">="&EOMONTH(TODAY(),-4)+1,G3:X3)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[leyan]

What version of excel are you using?

Microsoft 365 Apps for business

 

[leyan]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Microsoft 365 Apps for business and I have updated the Account details.

 

[leyan]

can try this instead,

=AVERAGE(INDEX(2:2,1,MATCH(9E+307,2:2)-2):INDEX(2:2,1,MATCH(9E+307,2it works

It works!!!
MATCH(9E+307,2:2) works great, thank you