Finding Average for Last 5/10 games closing line

Greetings everyone, was hoping for some help. I'm trying to find the formula to find the average closing line for a team in the last 5 games and 10 games. I've attached a workbook as an example.

In column A8:A32 is the situation where the team was either a favourite or underdog, in column B8:B32 is the closing line. SO I would like to find the average closing line in column B8:B32 for last 5 and last 10 games. However, one catch, in both cell A33 and B33 I have #N/A, and that is there because each day I past from another workbook if the game they just played was either F/D and the closing line. So how can I avoid the average with the #N/A?

Thanks and looking forward if anyone can help.

It worked!!! Apologies for the tardiness in getting back to you. Worked like a charm, thank you!

Originally Posted by etaf

i have played with a few formula

=AVERAGE(IF(IF($A$8:$A$40=E41,ROW($A$8:$A$40)-1,"")<>"",IF(IF($A$8:$A$40=E41,ROW($A$8:$A$40)-1)>=LARGE(IF($A$8:$A$40=E41,ROW($A$8:$A$40)-1),F41),$B$8:$B$40,""),""))

=AVERAGE(IF(ROW($A$8:$A$33)>=LARGE(IF($A$8:$A$33=E41,ROW($A$8:$A$33)),F41),IF($A$8:$A$33=E41,$B$8:$B$33)))

It worked!!! Apologies for the tardiness in getting back to you. Worked like a charm, thank you!

Originally Posted by FlameRetired

showtimesaints please take time to update your profile. 16.49 is not an MS product number.

Administrative Note:

Is your forum profile showing the version of Excel that you need this to work for?

Members will tailor the solutions they offer to the version of Office that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.

Sorry for the lateness in getting back. I updated my profile, I did as you recommended and the version I have is 16.67. It's Microsoft Excel for Mac. Hope this helps and sorry about the confusion.

Originally Posted by HansDouwe

I don't know what MS-Off Ver: 16.49 means, but if you have updated to MS-off 365 (version 2203 or newer) you can also try this formula:

Formula:

Please Login or Register  to view this content.

I have version 16.67. I'm trying this formula to, but am getting #NAME?. Am trying CTL+Shift+return but no luck.

Originally Posted by etaf

thats a 365 subscription

should see that in the about , info on the mac

also the version number in brackets -
have you updated recently

you should not have to use control - shift - enter as 365 version automatically applies arrays

seems to work for me

Ah, thanks! K I updated my profile with your help, thank you!