Question description:
This user has given permission to use the problem statement for this
blog.
How do I calculate the average for a group of data that is referenced to multiple cells? I.e. If I wanted to know the average salary of 10 employees with the same salary grade but in different departments.
Solved by O. J. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
12/09/2018 - 01:39
Welcome, Thanks for choosing Got It Pro-Excel.
Excelchat Expert
12/09/2018 - 01:40
Do you have sample data?
User
12/09/2018 - 01:41
yes
User
12/09/2018 - 01:41
i can provide
Excelchat Expert
12/09/2018 - 01:41
Okay. Please do...
Excelchat Expert
12/09/2018 - 01:44
Waiting....
User
12/09/2018 - 01:46
sorry
[Uploaded an Excel file]
Excelchat Expert
12/09/2018 - 01:46
Don't worry.
Excelchat Expert
12/09/2018 - 01:48
I am seeing 3 columns.
User
12/09/2018 - 01:48
I want to know the avgerage of column C for HOS only
User
12/09/2018 - 01:50
are you still there?
Excelchat Expert
12/09/2018 - 01:50
Yeah
Excelchat Expert
12/09/2018 - 01:51
Working on it.
Excelchat Expert
12/09/2018 - 01:52
There is a couple of ways you can achieve this.
User
12/09/2018 - 01:53
tell me more, please
Excelchat Expert
12/09/2018 - 01:53
You can use the AVERAGEIF function
Excelchat Expert
12/09/2018 - 01:53
DAVERAGE function
Excelchat Expert
12/09/2018 - 01:53
OR
Excelchat Expert
12/09/2018 - 01:53
You can use a PivotTable
Excelchat Expert
12/09/2018 - 01:54
Let us start with the AVERAGEIF function.
User
12/09/2018 - 01:55
okay
Excelchat Expert
12/09/2018 - 01:56
=AVERAGEIF($A$2:$A$253,$F$2,$C$2:$C$253)
Excelchat Expert
12/09/2018 - 01:56
Where:
Excelchat Expert
12/09/2018 - 01:56
$F$2 is the cell with the name of the PLEVEL
Excelchat Expert
12/09/2018 - 01:57
In this case "HOS"
Excelchat Expert
12/09/2018 - 01:59
AVERAGEIF function:
[Uploaded an Excel file]
Excelchat Expert
12/09/2018 - 02:00
Are you there?
User
12/09/2018 - 02:00
I am just trying to make sure i understand
Excelchat Expert
12/09/2018 - 02:00
You can check cell F3
Excelchat Expert
12/09/2018 - 02:00
That is where the function is.
Excelchat Expert
12/09/2018 - 02:00
Is there any questions or clarifications you may need in regards to this?
User
12/09/2018 - 02:01
If I was trying to pull the calculation into a different Sheet would I need to reference Data!A:A
Excelchat Expert
12/09/2018 - 02:02
What is the name of the sheet?
User
12/09/2018 - 02:02
Calculations
Excelchat Expert
12/09/2018 - 02:03
Calculations is the one with the data?
Excelchat Expert
12/09/2018 - 02:03
Data!$A$2:$A$253
Excelchat Expert
12/09/2018 - 02:03
And
Excelchat Expert
12/09/2018 - 02:03
Data!$C$2:$C$253
Excelchat Expert
12/09/2018 - 02:04
Both must reference to the same sheet.
Excelchat Expert
12/09/2018 - 02:04
Where Data is the sheet name of the sheet with the data.
User
12/09/2018 - 02:05
got it! thank you so much!!!
Excelchat Expert
12/09/2018 - 02:06
It was my pleasure helping you solve this problem. Any time you have a problem with Excel do not hesitate to come back for help.
Excelchat Expert
12/09/2018 - 02:06
Please remember to rate us at the end of the chat for better and quality services. Thank you!
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.