Returning averages using associated values among several datasets
I'm not completely sure how to explain it...I'm building a sales dashboard, and I need to calculate several different averages associated to certain associates (volume, units, etc). The data I receive isn't formatted in the easiest way, so I'm looking for a formula that:
- finds each time the associate's name is listed in column a (which can be multiple times if the associate has multiple loans) and then
- averages the data in column b associated with the associate and returns the average in column c.
So if Lacy Lender has 3 loans, it will find her name 3 times among the the dataset of 100, use the loan volume of each of her 3 loans (found in column b) and then return her average loan volume.
I'll need to run that formula for each LO.
The data is also found in a different sheet, but I do know how to reference different sheets in the formula.
Hopefully this is easy - can anyone help? Thank you!!
Answers
-
You can use SUMIF to total all the loan amounts in col b if the name in col A matches the one in the row.
=SUMIF([Col A]:[Col A], [Col A]@row, [Col B]:[Col B])
You can use COUNTIF to find the number of times the name in the row appears in col A.
=COUNTIF([Col A]:[Col A], [Col A]@row)
If you divide the SUM of loan amounts by the COUNT of loans, you will have the average loan amount.
So, your formula (in Col C) would be
=SUMIF([Col A]:[Col A], [Col A]@row, [Col B]:[Col B]) / COUNTIF([Col A]:[Col A], [Col A]@row)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 405 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!