Average last 3 values of a row but not include blank cells

Hello everyone,

I have searched and searched and thought I found an answer but didn't.

I am a golf coach and want to create a formula to average my golfers last three scores. However, not every player plays in each match, so some will have their last 3 scores be in a row where others may have blank cells inbetween.

I have the data set up in rows such as this example of the last 3 matches:

Golfer 1: 72, 72, 75, 78, 77
Golfer 2: 80, , 81, , 84
Golfer 3: 76, 77, 78, , 79

Again, I want to average the last three cells in the row that have a value in them.

Any suggestions?

Much appreciated,

Coach

Hi,

Assuming your first set of scores are in A1:E1, you could use this array formula**:

=SUM(IF(COLUMN(A1:E1)>=LARGE(IF(A1:E1<>"",COLUMN(A1:E1)),3),A1:E1))/3

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

You're welcome!

Cheers