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

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?


--
furryfishus
------------------------------------------------------------------------
furryfishus's Profile: http://www.excelforum.com/member.php...o&userid=34811
View this thread: http://www.excelforum.com/showthread...hreadid=545680

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

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

"furryfishus"
wrote in message
...

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?


--
furryfishus
------------------------------------------------------------------------
furryfishus's Profile:
http://www.excelforum.com/member.php...o&userid=34811
View this thread: http://www.excelforum.com/showthread...hreadid=545680

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

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