URGENT HELP, PEANUTS FOR THE EXCEL GURUS

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.



February 26th, 2015 1:41am

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.

Free Windows Admin Tool Kit Click here and download it now
February 26th, 2015 2:17am

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

February 26th, 2015 2:39am

I need a formula so that i may use to automate the answers, the target beneficiaries may not understand pivot tables
Free Windows Admin Tool Kit Click here and download it now
February 26th, 2015 2:50am

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

February 26th, 2015 3:51am

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))


Free Windows Admin Tool Kit Click here and download it now
February 26th, 2015 5:09am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics