LinkBack URL
About LinkBacks
Player W1 W2 W3 W4 W5 W6
Bob 10 20 30 BYE 50 60
Avg should be 40, closest I have works fine with blanks but not if there's text.
=SUM(INDEX(2:2,LARGE(INDEX((B2:Q2<>"")*COLUMN(B2:Q2),0),MIN(4,COUNT(B2:Q2)))):Q2)/4
Player W1 W2 W3 W4 W5 W6
Bob 10 20 30 BYE 50 60
Avg should be 40, closest I have works fine with blanks but not if there's text.
=SUM(INDEX(2:2,LARGE(INDEX((B2:Q2<>"")*COLUMN(B2:Q2),0),MIN(4,COUNT(B2:Q2)))):Q2)/4
Try changing
B2:Q2<>""
to
ISNUMBER(B2:Q2)
Sweet, that worked! Thank you!
You're welcome.
Try one of these...
=SUM(INDEX(2:2,LARGE(INDEX((ISNUMBER(B2:Q2)*COLUMN(B2:Q2)),0),MIN(4,COUNT(B2:Q2)))):Q2)/4
Or, this array formula**:
=SUM(INDEX(2:2,LARGE(IF(ISNUMBER(B2:Q2),COLUMN(B2:Q2)),MIN(4,COUNT(B2:Q2)))):Q2)/4
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks