Average First 3 Values

GBTIS
GBTIS used Ask the Experts™
on
I am trying to come up with a formula to average the first 3 values present in columns for individuals.  Attached is some sample data.  For Player 1 I basically want the average of 45,45 and 47.  For Player 2 I want 39,42 and 44 and Player 3 47,44 and 48.  Thanks for any help you can provide.
Average-First-3.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
In column G2, put in the following formula:
=AVERAGE(B2:D2)

That should do it.

Author

Commented:
That does not work if there is a blank value in the first 3 columns for a player.  I should have been more clear I want the average of the first 3 non blank values in a set of data for each player.
Try this ARRAY formula

=AVERAGE(OFFSET(A2,0,1,1,SMALL(IF(B2:F2<>"",COLUMN(B2:F2)),3)-1))

To enter an array formula press Ctrl-Shift-Enter

Author

Commented:
Thank you, worked perfectly.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial