Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|