Obtaining an average by typing the salesman you want to see and the column to AVG

[furryfishus]

The crux of my problem is that I have an excel sheet keeping track of
survey data (switching to access shortly). Right now the following
function lets me select a salesman and get an instant average of his
sales in the first column (I).

=AVERAGE(IF($C$2:$C$80=A84,$I$2:$I$80))


Currently, I type the name of the sales person in A84 and I get a
readout on his percentage in column I. Column I contains data from all
sales people and its all mixed together.

John 1
Steve 1
John 0
John 1
Larry 1
Sue 1
Sue 0

Name: [John] (all I do is type john and hit enter to get...)
0%

In my spreadsheet, there are about 10 columns worth of info. I will
only need to average their score within the same column, I don't need
to relate I to J. I would like to type

[Q1] in one cell
[John] in another and get
0% for the readout.

The purpose of this is to know at a glance how each sales person is
performing in each of 10 areas with only one number displayed at a
time.

Any suggestions?

 

[Bernard Liengme]

My suggestion is Pivot Table
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"furryfishus" <[email protected]>
wrote in message
news:[email protected]...

 

[Pete_UK]

You could try using INDIRECT - along the lines of:

=AVERAGE(IF($C$2:$C$80=A84,INDIRECT("$"&A83&"$2:$"&A83&"$80")))

where A83 contains a single letter for the column to be averaged
(e.g."Q") and A84 contains the name.

Hope this helps.

Pete