Lookup and Average?

[Colby]

I am assigning rowers to boats for a crew regatta. The
races are grouped by the average age of the rowers in a
boat. I need to experiment with different combinations of
rowers and easily see the average age of the line-up I
have created for each boat.

C1:Z1 = Names of rowers
C2:Z2 = Ages of rowers
A3:a50 = name of event
C3:Z50 = a grid in which I enter a seat number (1 to 4 or
8) or just an "X" (before the exact lineup is decided) if
that rower is assigned to row in that race.

What I want in B3:B50 is a formula that will compute the
average age of the rowers I have assigned to each boat,
and re-compute it when I change the line-up (by placing
numbers or "X"s under different rower names on that boat's
line in the grid).

Age: 30 40 50 65
Avg.
Race 1 40.00 4 2 3

Race 2 57.50 X X

Race 3 ???? 4 1 3
Etc.
Thanks -- This will save me hours!

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER) in cell
V3:
=AVERAGE(IF($C3:$Z50<>""),$C$2:$Z$2))
and copy this down

 

[Guest]

If that doesn't work, try this:
=(($C$2*C3)+($D$2*D3)+($E$2*E3)+($F$2*F3))/SUM(C3:F3)

Put that in your B column and just copy and paste it as
far down the B column as you need it.

The one thing is that it will origionally give you a
#DIV/0! error because you are dividing this stuff by
nothing as you haven't entered data. Once you enter
numbers it will kick in. The other thing is that you
can't put the * in the fields like you had mentioned or
you will get another error because you can't caluculate
text.

 

[BillJunior]

EEP! Do not do that last one. I accidentally clicked send
instead of cancel. It would work but if you did it for
lots of names it would get ugly to type in. Sorry about
that.

 

[Guest]

Is the Ctrl+Shift+Enter a way to tell Excel to calculate
the ranges all seperately?

 

[Aladin Akyurek]

B3:

=AVERAGE(IF(1-ISBLANK(C3:Z3),$C$2:$Z$2))

Confirm this with control+shift+enter instead of just with enter & copy
down.

By the repeated deleting & retyping you might get cells that look empty but
not really empty. The following formula would capture dirty input better:

=AVERAGE(IF(TRIM(C3:Z3)<>"",$C$2:$Z$2))

which also needs to be confirmed with control+shift+enter before copying
down.

 

[Harlan Grove]

B3:

=AVERAGE(IF(1-ISBLANK(C3:Z3),$C$2:$Z$2))

Confirm this with control+shift+enter instead of just with enter & copy
down.

....

One alternative involving fewer function calls and not needing to be an
array formula would be

=SUMIF(C3:Z3,"<>",$C$2:$Z$2)/COUNTIF(C3:Z3,"<>")

 

[Aladin Akyurek]

...

One alternative involving fewer function calls and not needing to be an
array formula would be

=SUMIF(C3:Z3,"<>",$C$2:$Z$2)/COUNTIF(C3:Z3,"<>")

Sure. I had a motive though for not proposing SumIf/CountIf or
=AVERAGE(IF(C3:F3<>"",$C$2:$F$2))...

 

[Frank Kabel]

Hi
CTRL+SHIFT+ENTER is used to enter an array formula. See:
http://www.cpearson.com/excel/array.htm