Home |
Search |
Today's Posts |
#1
|
|||
|
|||
comparing two columns & then perform a calculation
Hi,
I am a teacher analysing attendance figures - and struggling somewhat! Here's what I want to do . . . I have a spreadsheet with the following columns: A B C D Name (student) Year (7-11) attended (Y or N) % attendance I want to enter a formula somewhere else on the sheet that will; select only students in Year 7 (by looking this up in column B), calculate the average % attendance for these students only. I will then edit this formula (in a different cell) to perform the same calculation for year 8 students, then year 9 students . . . and so on. I want to keep running totals so this won't be just a once-only calculation. As more students get added to the list I want the attendance results to update. Please help me! |
#2
|
|||
|
|||
One way ..
List in H1 across the year: 7, 8, 9, 10 ... Put in H2: =SUMPRODUCT(($B$2:$B$100=H1)*($C$2:$C$100="Y"))/COUNTIF($B$2:$B$100,H1) Format H2 as percentage and copy across as required H2 will return the %attendance for year 7, I2 returns year 8's, and so on Adapt the ranges to suit .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "dazp1970" wrote in message ... Hi, I am a teacher analysing attendance figures - and struggling somewhat! Here's what I want to do . . . I have a spreadsheet with the following columns: A B C D Name (student) Year (7-11) attended (Y or N) % attendance I want to enter a formula somewhere else on the sheet that will; select only students in Year 7 (by looking this up in column B), calculate the average % attendance for these students only. I will then edit this formula (in a different cell) to perform the same calculation for year 8 students, then year 9 students . . . and so on. I want to keep running totals so this won't be just a once-only calculation. As more students get added to the list I want the attendance results to update. Please help me! |
#3
|
|||
|
|||
=SUMIF(B2:B100,7,D2:D100)/COUNTIF(B2:B100,7)
-- Ian -- "dazp1970" wrote in message ... Hi, I am a teacher analysing attendance figures - and struggling somewhat! Here's what I want to do . . . I have a spreadsheet with the following columns: A B C D Name (student) Year (7-11) attended (Y or N) % attendance I want to enter a formula somewhere else on the sheet that will; select only students in Year 7 (by looking this up in column B), calculate the average % attendance for these students only. I will then edit this formula (in a different cell) to perform the same calculation for year 8 students, then year 9 students . . . and so on. I want to keep running totals so this won't be just a once-only calculation. As more students get added to the list I want the attendance results to update. Please help me! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing text in columns | Excel Discussion (Misc queries) | |||
Perform functions on the result of adding two columns | Excel Worksheet Functions | |||
Comparing Data in 2 columns | Excel Worksheet Functions | |||
Comparing data in two columns and highlighting the data | Excel Worksheet Functions | |||
Comparing Data in two columns | Excel Worksheet Functions |