Average cells based on column data

fordmudslinger

Board Regular
Joined
Apr 4, 2015
Messages
64
Hi, I have a database of customers and how long we are at their property each time we visit it thru the year. In column A is the customers name and column g is the amount of time we are there. Now in column a the customers name is listed each time we visited their property, each customer is listed different amounts of time. One customer may have been serviced 8 times during the year and some 20 times. Regardless, their name is in column a however many times we serviced them followed by other data in the same row regarding their property.

Is there a way to average column g for each customer to find average of time? It would have to look at column a and see the customers name and pull only the time from column g for that customer, return the result to column l and move on to the next customer.

Thanks!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,682
Office Version
  1. 365
Platform
  1. Windows
I would turn your data into a pivot table.

Select your data, hit Ctrl+N+Z, or click insert Pivot Table.

Then add Customer name to Rows. Add Time Spent to Values. Then change the Field Value Settings for the Time spent to be Average instead of the default of Sum.
 
Upvote 0

fordmudslinger

Board Regular
Joined
Apr 4, 2015
Messages
64
I would turn your data into a pivot table.

Select your data, hit Ctrl+N+Z, or click insert Pivot Table.

Then add Customer name to Rows. Add Time Spent to Values. Then change the Field Value Settings for the Time spent to be Average instead of the default of Sum.

That works perfectly...thanks so much; great help!
 
Upvote 0

Forum statistics

Threads
1,190,911
Messages
5,983,525
Members
439,848
Latest member
timmyo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top