Question description:
This user has given permission to use the problem statement for this
blog.
I need to find a formula to recognize a particular name in a column and associate the numbers (row) with the name. add up all the numbers with the name and divide by how many times the name comes up.
Solved by V. D. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
16/08/2018 - 07:07
Hi
User
16/08/2018 - 07:08
Hey CV. im trying to create a average
Excelchat Expert
16/08/2018 - 07:08
Would you be able to share the data?
User
16/08/2018 - 07:08
sure
User
16/08/2018 - 07:08
how
Excelchat Expert
16/08/2018 - 07:09
you can upload the file here
User
16/08/2018 - 07:09
[Uploaded an Excel file]
User
16/08/2018 - 07:10
like this ?
Excelchat Expert
16/08/2018 - 07:10
yes
Excelchat Expert
16/08/2018 - 07:10
Which sheet do you want the formula to be in ?
User
16/08/2018 - 07:10
well for some reason my screen has gone blank. no more excel spreadsheet anymore
User
16/08/2018 - 07:11
average
Excelchat Expert
16/08/2018 - 07:11
Average of what column and in which sheet?
User
16/08/2018 - 07:11
im trying to create a formula that will take each technicians name as i enter them every day.
User
16/08/2018 - 07:11
DATA
Excelchat Expert
16/08/2018 - 07:11
okay
Excelchat Expert
16/08/2018 - 07:12
what is the value you want to average?
User
16/08/2018 - 07:12
is the sheet. i would like to create a formula that will take each technicians name . add up the revenue per call and divide it by the amount of call the technician has.
User
16/08/2018 - 07:12
is that clear enough ?
Excelchat Expert
16/08/2018 - 07:13
yes
Excelchat Expert
16/08/2018 - 07:13
SO , you want to average revenue per call?
User
16/08/2018 - 07:13
yes
Excelchat Expert
16/08/2018 - 07:13
Column F
Excelchat Expert
16/08/2018 - 07:13
in which column should i put this formula in ?
Excelchat Expert
16/08/2018 - 07:14
or should I create a separate table for technician and average?
User
16/08/2018 - 07:14
create a spreadsheet
Excelchat Expert
16/08/2018 - 07:14
sure
Excelchat Expert
16/08/2018 - 07:14
ve me sometime to work on it
Excelchat Expert
16/08/2018 - 07:14
*give
Excelchat Expert
16/08/2018 - 07:17
Here it is
[Uploaded an Excel file]
User
16/08/2018 - 07:17
technicians name by the amount of (total) revenue he has made divided by the (total) calls hes had. i input around 15 new rows everyday. so if i can get this to work it would save me a ton of time.
Excelchat Expert
16/08/2018 - 07:18
So, if you want to update this, just enter in the immediate rows in the data sheet
User
16/08/2018 - 07:18
and automaticle update
Excelchat Expert
16/08/2018 - 07:18
nd then refresh the pivot table
Excelchat Expert
16/08/2018 - 07:18
*and
User
16/08/2018 - 07:18
what formula did you use ?
Excelchat Expert
16/08/2018 - 07:18
I used pivot table
Excelchat Expert
16/08/2018 - 07:18
No formula
Excelchat Expert
16/08/2018 - 07:19
Does this answer your question?
User
16/08/2018 - 07:20
you did it. and if i enter new data in the sheet and hit refresh. the pivot table will also refresh?
Excelchat Expert
16/08/2018 - 07:21
You enter the data in the row just below the where previous data
Excelchat Expert
16/08/2018 - 07:21
Suppose, the current data ends in row 100
Excelchat Expert
16/08/2018 - 07:21
Then you would have to enter the data in row 101
Excelchat Expert
16/08/2018 - 07:21
This will help excel to understand if they are part of the same table
User
16/08/2018 - 07:22
awesome, i get that. just how did you create this in the pivot table ?
User
16/08/2018 - 07:22
no formula ?
Excelchat Expert
16/08/2018 - 07:23
Post this, go to the sheet with pivot table, select any cell in the pivot table >then Pivot table analyze tab in the top and refresh
Excelchat Expert
16/08/2018 - 07:23
yes, no formula was used
Excelchat Expert
16/08/2018 - 07:24
For creating pivot table select the data > Insert Tab > Pivot table
User
16/08/2018 - 07:25
so if i where to graph this . then entered mor data in my data sheet the graph would automatically change if i press refresh
Excelchat Expert
16/08/2018 - 07:26
yes
Excelchat Expert
16/08/2018 - 07:27
but you would have to insert pivot graph
Excelchat Expert
16/08/2018 - 07:27
and this functionality is available in windows only
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.