Average of 3 pieces of data from within a row

**Virant518** · 04-12-2010, 05:19 PM

So I'm working on building an automated score sheet for a team.

I'm currently using the formula

Please Login or Register  to view this content.

To pull and average the latest three entries in each row.

The scores are needing to be based on a handicap system- the average of the previous 3 scores get subracted from the most recent and that number added to 1000, to find the handicapped score. Right now I'm using the spreadsheet to get the average, but it's also pulling from the most recent score, which is not desired.

For example,in the first four events in a season, a person scores A,B,C, and D. What I would like to have happen is to be able to enter the scores and have the spreadsheet automatically average the A,B,and C while saving the D for the overall handicapped score calculation.

My formula above is unfortunately taking the average of B,C,and D. Is there any way to have excel pull the 2nd most recent, 3rd most recent, and 4th most recent? Or possibly to have an exemption for the 1st most recent?

**teylyn** · 04-12-2010, 07:43 PM

Hello Virant, welcome to the forum.

You are using the Match function to find the last number in the row. With that technique, you can also find several columns offset to the left

=ROUND(AVERAGE(INDEX(B2:M2,MATCH(9.99999999999999E+307,B2:M2)-3):INDEX(B2:M2,MATCH(9.99999999999999E+307,B2:M2)-1)),0)

**Virant518** · 04-12-2010, 08:12 PM

Originally Posted by teylyn

Hello Virant, welcome to the forum.

You are using the Match function to find the last number in the row. With that technique, you can also find several columns offset to the left

=ROUND(AVERAGE(INDEX(B2:M2,MATCH(9.99999999999999E+307,B2:M2)-3):INDEX(B2:M2,MATCH(9.99999999999999E+307,B2:M2)-1)),0)

Thanks so much for the help, I wasn't aware this was possible.

Now, in playing with this, I noticed that it's literally jumping over a column- the issue with this is that not every player attends every event, but the handicap system still uses their last 3 events that they attended. Is it possible to have it jump over to a non-vacant cell instead of just the next column?

**teylyn** · 04-12-2010, 10:12 PM

Can you post a file with some sample data and give the expected results manually? You can upload a file by clicking "Go Advanced" below the Quick Reply box and then the paper clip icon.

cheers

**Virant518** · 04-12-2010, 10:14 PM

Originally Posted by teylyn

Can you post a file with some sample data and give the expected results manually? You can upload a file by clicking "Go Advanced" below the Quick Reply box and then the paper clip icon.

cheers

Sure, it's attached now.

Expected results and a few notes are at the bottom of the spreadsheet.

**teylyn** · 04-13-2010, 02:01 AM

OK,

it's a bit more difficult than a simple offset. Try this on your pianola. In Q2

Please Login or Register  to view this content.

This is an array formula and must be confirmed with Ctrl-Shift-Enter, then copy down.

It will calculate the average of the last three scores before the last score, ignoring blank columns. The formula is set up to take into account Event1 through to Event12.

Let me know if that works for you.

cheers

**Virant518** · 04-13-2010, 02:33 AM

Originally Posted by teylyn

OK,

it's a bit more difficult than a simple offset. Try this on your pianola. In Q2

Please Login or Register  to view this content.

This is an array formula and must be confirmed with Ctrl-Shift-Enter, then copy down.

It will calculate the average of the last three scores before the last score, ignoring blank columns. The formula is set up to take into account Event1 through to Event12.

Let me know if that works for you.

cheers

That one works wonderfully! Thanks so very much!