I have a table, where Column1 is of the ages of students, Column 2 is of their test results. i want to fin the average score of students between ages say 18- 30. Please help with a formula.
Technology Tips and News
I have a table, where Column1 is of the ages of students, Column 2 is of their test results. i want to fin the average score of students between ages say 18- 30. Please help with a formula.
You can do it in several ways.
The direct approach is to use AVERAGEIF.
Another approach is to use PivotTable:
1- Select a cell in one of your columns.
2- Click Insert->PivotTable, and click OK in the PivotTable dialog.
3- Drag Age column to ROWS and Grade column to VALUES.
4- Change calculation of grade from Sum to Average.
5- Use the filter on age column to select the desired age, or use the Grouping feature (right click on any age cell, and select Group) to define the age groups you need.
Hi,
Easiest way would be to use an AVERAGEIFS function here. See example below:
=AVERAGEIFS(B1:B10, A1:A10,">18",A1:A10,"<30")
Explanation:
The first range is the range where the resuts are (in your case)
The second range is the range where the ages are
Then you have to set the first criteria being >18
Then you set the first ranges with ages again and finally you set the second criteria in your case <30
So tweak as you need and you are good to go.
Maurice
Thanks big time it worked so well. is there a way i can use the cell value rather than setting a defined value instead of >18 I use >C3?? i have tried using the cell and it hasnt worked
You can achieve it with the INDIRECT function.
Assuming you have in D1 the value ">18" and in E1 the value "<30".
You can write:
=AVERAGEIFS(B1:B10, A1:A10,INDIRECT("D1",TRUE),A1:A10,INDIRECT("E1",TRUE))