Counting select numbers in a row then averaging them

[sobrien]

This is a bit tricky but I think it could be done. I have player ratings for each game of the season as well as DNP for games they "Did not play" in. I want to take the average of the best four of the last 5 games they played in however the letters "DNP" may be somewhere in that last 5 games in which case I need the formula to disregard cells with DNP in them and just look for ones with numbers in them. Also if DNP was in their very last game I want to take thesame formula with a 20% reduction.

To show an example in row1
98 99 79 DNP 76 DNP 55 92

In this case the answer should be (92+76+79+99)/4 = 86.5 being the average of the best four from the last 5 games played

In row2
96 101 76 DNP 36 99 DNP DNP DNP
In this case the same formula abovbe but with a 20% reduction as the player DNP in his last game and is returning (most likely from injury and may not be fully fit)
The answer would be (99+76+101+86)/4*80%= 72.4 being the formula above with a 20% reduction

There is also one more trick to this. I need the fomula in row Z with the ability to look up data from row C:X which represents 24 games for the season. The data in these cells will be filled each week as the player gets a rating so the cells will be blank until that game is played.

gee I hope that makes sense

Thanks in advance

 

[Weaver]

where would you store the info regarding the 'fitness ratio'? Is there a column for that? If not, there probably should be.

 

[sobrien]

thanks. I am familiary with arrays. is their an array formula that would onoly add up the best 4 of the last 5 cells with positive numbers them?

 

[Weaver]

I think it's an array solution, but I'm having a bit of trouble getting my head around it myself!

 

[sobrien]

I think I need to somehow get the last 5 numbers in the row so they are side by side and then use an array?? I just put up a new post so check it out