Calculate with indexing

[americanpie3]

Hello,

As part of a dart league we calculate averages, number of closes and also compare stats week to week.

I have 1 worksheet that I put in the lineups and all their scores and finishes. Then on my summary work sheet I have the player's name, season average, last week's average, total finishes, last week finishes

I have this formula on my main sheet for looking up which works just perfectly =INDEX(Week1!Z1:Z60,MATCH(A20,Week1!A1:A60,0)) which I use to lookup last week's average

What I would love is to have is a marriage of index and match to go with this formula =IFERROR(AVERAGE(Week1!B2:X2,Week2!B2:X2),0) for the season averages. This means that the player's name and scores must be on that row.

As you can see in the 2nd formula, it does not allow my sheet to be dynamic.

I hope I've explained myself clearly enough.

Thank you all.

 

[Robert Mika]

Excel has its own AVERAGEIFS function ,try this and let me how it goes.
Otherwise provide a layout of your data

 

[americanpie3]

Hi Robert,

I'm now sure how the averageif works. I've tried it and it doesn't seem to give me the results I want.

What details would you need of my layout?

 

[Robert Mika]

The way the data are on spreadsheet

 

[americanpie3]

I have a workbook called Weekx and another called SeasonSummaryx

I enter all of my player's scores that day on Weekx. Then on that sheet I then have 5 columns. Season average, week's average, High Score, Finish and highest finish.

Now at one week Player Jason's score would be in the range of =IFERROR(AVERAGE($B2:$X2),0). The next week he could be in the range of =IFERROR(AVERAGE($B22:$X22),0).

Having these two formulas together would give me his average regardless if he misses a week or not with =IFERROR(AVERAGE(Week1!B2:X2,Week2!B2:X2,Week3!B22:X22,Week4!B17:X17),0) but it doesn't make my excel sheet very dynamic which is my main goal. So no matter where Jason is on my weekX spreadsheet his Season average will be calculated.

I think that pretty much covers it.

Thanks again