[SOLVED] Google Sheets help - multiple criteria in one formula - last x results, exclude y, average

[gvmsia]

hi GS experts, I have a sheet of game scores and am trying to get a formula with multiple criteria....

1. only look at the last 10 scores
2. exclude the highest and lowest scores
3. average the remaining 8 scores

so in the case of Kirby vs Tony (col A), i would like the formula to look at only the last 10 games (row 7 to 16), then exclude the highest and lowest scores (7 and -6), then average the remaining 8 scores (24/8). result is 3

 

[Deleted member 14196]

Looks like a scripting job. No canned formula can do that.

https://developers.google.com/apps-script/guides/sheets/functions

 

[Ralston18]

Agree with @Mandark.

Functions can be certainly be used.

I will add the suggestion that you break down the requirements into 3 (three) different formulas and/or functions even if there is some duplication amongst them.

Focus on each requirement individually, get it to work, and test accordingly.

Very good chance that if you manage one "all singing, all dancing" formula some change will come along and it will all blow up on you.

At some point all will become cumbersome, provide no results, or (even worse) provide the wrong results.

Three simpler functions recommended.

Occam's Razor applies.

 

[gvmsia]

Looks like a scripting job. No canned formula can do that.

https://developers.google.com/apps-script/guides/sheets/functions

tq. i will look into that

 

[gvmsia]

Agree with @Mandark.

Functions can be certainly be used.

I will add the suggestion that you break down the requirements into 3 (three) different formulas and/or functions even if there is some duplication amongst them.

Focus on each requirement individually, get it to work, and test accordingly.

Very good chance that if you manage one "all singing, all dancing" formula some change will come along and it will all blow up on you.

At some point all will become cumbersome, provide no results, or (even worse) provide the wrong results.

Three simpler functions recommended.

Occam's Razor applies.

tq. and noted. u are right. that would be easier to id problems