Hi,
I have a spreadsheet which lists students, their instructors names, actual hours of teaching and a calculation (diff).
I need to be able to do two things:
1. Create a summary for each instructor which counts the number of times each instructors time is over 0.2 (in the 'Diff' column) for all students. I guess that means having a function that uses a vlookup to find the instructors name and counts only if the 'Diff' column is over 0.2?
2. Create a summary for each instructor which returns a list of the numbers which are over 0.2 in the Diff column so we can see what the amount over each time the instructor is going?
Is this possible or am I going about finding this information in a long-winded way?
I also want to ensure that if cells are blank these functions don't return errors.
[TABLE="width: 616"]
<tbody>[TR]
[TD="colspan: 3"]Gian
[/TD]
[TD="colspan: 3"]Johnson
[/TD]
[TD="colspan: 3"]Koln
[/TD]
[/TR]
[TR]
[TD]Instructor
[/TD]
[TD]Actual Hours
[/TD]
[TD]Diff
[/TD]
[TD]Instructor
[/TD]
[TD]Actual Hours
[/TD]
[TD]Diff
[/TD]
[TD]Instructor
[/TD]
[TD]Actual Hours
[/TD]
[TD]Diff
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[/TR]
[TR]
[TD]G.LIU
[/TD]
[TD]1.4
[/TD]
[TD]0.0
[/TD]
[TD]G.LIU
[/TD]
[TD]1.4
[/TD]
[TD]0
[/TD]
[TD]G.LIU
[/TD]
[TD]1.4
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]G.LIU
[/TD]
[TD]1.4
[/TD]
[TD]0.0
[/TD]
[TD]G.LIU
[/TD]
[TD]1.4
[/TD]
[TD]0
[/TD]
[TD]G.LIU
[/TD]
[TD]1.4
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]G.LIU
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[TD]G.LIU
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[TD]G.LIU
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[/TR]
[TR]
[TD]G.LIU
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[TD]G.LIU
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[TD]G.LIU
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]0.5
[/TD]
[TD]-0.7
[/TD]
[TD]S.KING
[/TD]
[TD]0.5
[/TD]
[TD]-0.7
[/TD]
[TD]S.KING
[/TD]
[TD]0.5
[/TD]
[TD]-0.7
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.4
[/TD]
[TD]0.0
[/TD]
[TD]S.KING
[/TD]
[TD]1.4
[/TD]
[TD]0
[/TD]
[TD]S.KING
[/TD]
[TD]1.4
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.1
[/TD]
[TD]-0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.1
[/TD]
[TD]-0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.1
[/TD]
[TD]-0.1
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.1
[/TD]
[TD]-0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.1
[/TD]
[TD]-0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.1
[/TD]
[TD]-0.1
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.3
[/TD]
[TD]0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.3
[/TD]
[TD]0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.3
[/TD]
[TD]0.1
[/TD]
[/TR]
[TR]
[TD]B.CAMPBELL
[/TD]
[TD]1.3
[/TD]
[TD]0.1
[/TD]
[TD]B.CAMPBELL
[/TD]
[TD]1.3
[/TD]
[TD]0.1
[/TD]
[TD]B.CAMPBELL
[/TD]
[TD]1.3
[/TD]
[TD]0.1
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.3
[/TD]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.3
[/TD]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.3
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.3
[/TD]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.3
[/TD]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.3
[/TD]
[/TR]
[TR]
[TD]B.CAMPBELL
[/TD]
[TD]1.4
[/TD]
[TD]0.2
[/TD]
[TD]B.CAMPBELL
[/TD]
[TD]1.4
[/TD]
[TD]0.2
[/TD]
[TD]B.CAMPBELL
[/TD]
[TD]1.4
[/TD]
[TD]0.2
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]0.9
[/TD]
[TD]0.1
[/TD]
[TD]S.KING
[/TD]
[TD]0.9
[/TD]
[TD]0.1
[/TD]
[TD]S.KING
[/TD]
[TD]0.9
[/TD]
[TD]0.1
[/TD]
[/TR]
[TR]
[TD]SELF
[/TD]
[TD]1.3
[/TD]
[TD]0.1
[/TD]
[TD]SELF
[/TD]
[TD]1.3
[/TD]
[TD]0.1
[/TD]
[TD]SELF
[/TD]
[TD]1.3
[/TD]
[TD]0.1
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.2
[/TD]
[TD]0.0
[/TD]
[TD]S.KING
[/TD]
[TD]1.2
[/TD]
[TD]0
[/TD]
[TD]S.KING
[/TD]
[TD]1.2
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]0.9
[/TD]
[TD]-0.1
[/TD]
[TD]S.KING
[/TD]
[TD]0.9
[/TD]
[TD]-0.1
[/TD]
[TD]S.KING
[/TD]
[TD]0.9
[/TD]
[TD]-0.1
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.2
[/TD]
[TD]0.0
[/TD]
[TD]S.KING
[/TD]
[TD]1.2
[/TD]
[TD]0
[/TD]
[TD]S.KING
[/TD]
[TD]1.2
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.3
[/TD]
[TD]0.0
[/TD]
[TD]S.KING
[/TD]
[TD]1.3
[/TD]
[TD]0
[/TD]
[TD]S.KING
[/TD]
[TD]1.3
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.3
[/TD]
[TD]-0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.3
[/TD]
[TD]-0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.3
[/TD]
[TD]-0.1
[/TD]
[/TR]
[TR]
[TD]M.DJOUHRI
[/TD]
[TD]1.3
[/TD]
[TD]-0.3
[/TD]
[TD]M.DJOUHRI
[/TD]
[TD]1.3
[/TD]
[TD]-0.3
[/TD]
[TD]M.DJOUHRI
[/TD]
[TD]1.3
[/TD]
[TD]-0.3
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.4
[/TD]
[TD]-0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.4
[/TD]
[TD]-0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.4
[/TD]
[TD]-0.1
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]2.2
[/TD]
[TD]0.7
[/TD]
[TD]S.KING
[/TD]
[TD]2.2
[/TD]
[TD]0.7
[/TD]
[TD]S.KING
[/TD]
[TD]2.2
[/TD]
[TD]0.7
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1
[/TD]
[TD]0.0
[/TD]
[TD]S.KING
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]S.KING
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]0
[/TD]
[TD]-1.0
[/TD]
[TD]S.KING
[/TD]
[TD]0
[/TD]
[TD]-1
[/TD]
[TD]S.KING
[/TD]
[TD]0
[/TD]
[TD]-1
[/TD]
[/TR]
</tbody>[/TABLE]
I'm not sure how to add an example spreadsheet so have pasted an example of the data above.
TIA
I have a spreadsheet which lists students, their instructors names, actual hours of teaching and a calculation (diff).
I need to be able to do two things:
1. Create a summary for each instructor which counts the number of times each instructors time is over 0.2 (in the 'Diff' column) for all students. I guess that means having a function that uses a vlookup to find the instructors name and counts only if the 'Diff' column is over 0.2?
2. Create a summary for each instructor which returns a list of the numbers which are over 0.2 in the Diff column so we can see what the amount over each time the instructor is going?
Is this possible or am I going about finding this information in a long-winded way?
I also want to ensure that if cells are blank these functions don't return errors.
[TABLE="width: 616"]
<tbody>[TR]
[TD="colspan: 3"]Gian
[/TD]
[TD="colspan: 3"]Johnson
[/TD]
[TD="colspan: 3"]Koln
[/TD]
[/TR]
[TR]
[TD]Instructor
[/TD]
[TD]Actual Hours
[/TD]
[TD]Diff
[/TD]
[TD]Instructor
[/TD]
[TD]Actual Hours
[/TD]
[TD]Diff
[/TD]
[TD]Instructor
[/TD]
[TD]Actual Hours
[/TD]
[TD]Diff
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[/TR]
[TR]
[TD]G.LIU
[/TD]
[TD]1.4
[/TD]
[TD]0.0
[/TD]
[TD]G.LIU
[/TD]
[TD]1.4
[/TD]
[TD]0
[/TD]
[TD]G.LIU
[/TD]
[TD]1.4
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]G.LIU
[/TD]
[TD]1.4
[/TD]
[TD]0.0
[/TD]
[TD]G.LIU
[/TD]
[TD]1.4
[/TD]
[TD]0
[/TD]
[TD]G.LIU
[/TD]
[TD]1.4
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]G.LIU
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[TD]G.LIU
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[TD]G.LIU
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[/TR]
[TR]
[TD]G.LIU
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[TD]G.LIU
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[TD]G.LIU
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]0.5
[/TD]
[TD]-0.7
[/TD]
[TD]S.KING
[/TD]
[TD]0.5
[/TD]
[TD]-0.7
[/TD]
[TD]S.KING
[/TD]
[TD]0.5
[/TD]
[TD]-0.7
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.4
[/TD]
[TD]0.0
[/TD]
[TD]S.KING
[/TD]
[TD]1.4
[/TD]
[TD]0
[/TD]
[TD]S.KING
[/TD]
[TD]1.4
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.1
[/TD]
[TD]-0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.1
[/TD]
[TD]-0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.1
[/TD]
[TD]-0.1
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.1
[/TD]
[TD]-0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.1
[/TD]
[TD]-0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.1
[/TD]
[TD]-0.1
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.3
[/TD]
[TD]0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.3
[/TD]
[TD]0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.3
[/TD]
[TD]0.1
[/TD]
[/TR]
[TR]
[TD]B.CAMPBELL
[/TD]
[TD]1.3
[/TD]
[TD]0.1
[/TD]
[TD]B.CAMPBELL
[/TD]
[TD]1.3
[/TD]
[TD]0.1
[/TD]
[TD]B.CAMPBELL
[/TD]
[TD]1.3
[/TD]
[TD]0.1
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.3
[/TD]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.3
[/TD]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.3
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.3
[/TD]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.3
[/TD]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.3
[/TD]
[/TR]
[TR]
[TD]B.CAMPBELL
[/TD]
[TD]1.4
[/TD]
[TD]0.2
[/TD]
[TD]B.CAMPBELL
[/TD]
[TD]1.4
[/TD]
[TD]0.2
[/TD]
[TD]B.CAMPBELL
[/TD]
[TD]1.4
[/TD]
[TD]0.2
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]0.9
[/TD]
[TD]0.1
[/TD]
[TD]S.KING
[/TD]
[TD]0.9
[/TD]
[TD]0.1
[/TD]
[TD]S.KING
[/TD]
[TD]0.9
[/TD]
[TD]0.1
[/TD]
[/TR]
[TR]
[TD]SELF
[/TD]
[TD]1.3
[/TD]
[TD]0.1
[/TD]
[TD]SELF
[/TD]
[TD]1.3
[/TD]
[TD]0.1
[/TD]
[TD]SELF
[/TD]
[TD]1.3
[/TD]
[TD]0.1
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.2
[/TD]
[TD]0.0
[/TD]
[TD]S.KING
[/TD]
[TD]1.2
[/TD]
[TD]0
[/TD]
[TD]S.KING
[/TD]
[TD]1.2
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]0.9
[/TD]
[TD]-0.1
[/TD]
[TD]S.KING
[/TD]
[TD]0.9
[/TD]
[TD]-0.1
[/TD]
[TD]S.KING
[/TD]
[TD]0.9
[/TD]
[TD]-0.1
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.2
[/TD]
[TD]0.0
[/TD]
[TD]S.KING
[/TD]
[TD]1.2
[/TD]
[TD]0
[/TD]
[TD]S.KING
[/TD]
[TD]1.2
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.3
[/TD]
[TD]0.0
[/TD]
[TD]S.KING
[/TD]
[TD]1.3
[/TD]
[TD]0
[/TD]
[TD]S.KING
[/TD]
[TD]1.3
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.3
[/TD]
[TD]-0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.3
[/TD]
[TD]-0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.3
[/TD]
[TD]-0.1
[/TD]
[/TR]
[TR]
[TD]M.DJOUHRI
[/TD]
[TD]1.3
[/TD]
[TD]-0.3
[/TD]
[TD]M.DJOUHRI
[/TD]
[TD]1.3
[/TD]
[TD]-0.3
[/TD]
[TD]M.DJOUHRI
[/TD]
[TD]1.3
[/TD]
[TD]-0.3
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.4
[/TD]
[TD]-0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.4
[/TD]
[TD]-0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.4
[/TD]
[TD]-0.1
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[TD]S.KING
[/TD]
[TD]1.5
[/TD]
[TD]0.1
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]2.2
[/TD]
[TD]0.7
[/TD]
[TD]S.KING
[/TD]
[TD]2.2
[/TD]
[TD]0.7
[/TD]
[TD]S.KING
[/TD]
[TD]2.2
[/TD]
[TD]0.7
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]1
[/TD]
[TD]0.0
[/TD]
[TD]S.KING
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]S.KING
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]S.KING
[/TD]
[TD]0
[/TD]
[TD]-1.0
[/TD]
[TD]S.KING
[/TD]
[TD]0
[/TD]
[TD]-1
[/TD]
[TD]S.KING
[/TD]
[TD]0
[/TD]
[TD]-1
[/TD]
[/TR]
</tbody>[/TABLE]
I'm not sure how to add an example spreadsheet so have pasted an example of the data above.
TIA