Average

[Andrew C]

Hi

Below is a sample of my spreadsheet

Lane Name Game 1 Game 2
1 Joe 199 201
11 Sam 202 170
15 Tom 157 160
1 Jane 143 134
11 Bob 160 170
1 Tony 234 254

The Information will be entered in random order. I want
to get an average of the scores on Lane 1. I need a
formula to work out the averages for every lane and place
in another cell with i will be wanting to graph this
information. I am not sure how times lane 1 will appear.

Hope this is enough information

Thanks


Andrew C

 

[Biff]

Hi Andrew,

Here's one way:

=AVERAGE(IF(A1:A6=1,C16))

Entered as an array - CTRL+SHIFT+ENTER

To make this more versatile, you can use a cell to enter
the lane number and then use that cell reference in the
formula.

Biff

 

[Andrew C]

Thanks Biff

It works OK when edting the formula, but when you save it
and move off onto another cell it brings up #Value!.

Can you tell me if i have done anything wrong.

Cheers
Andrew C

 

[Biff]

Hi Andrew,

Because that is an array formula, *everytime* that you
edit it, you must re-enter it as an array by holding down
CTRL SHIFT and then hit ENTER. When you do that XL will
place squiggly brackets {} around the formula indicating
that it is an array.

Biff