| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
New Twist on Old Question-Averages
Refresher: My Excel 2000 workbook has two sheets, the first (Evaluation
Detail) contains details about course evalutions per instructor. Column A is Course Name, B and C are Course Numbers, D is Course Date, E is Training Provider, F is Instuctor Name, G thru P are evaluation scores and Q is Evaluation Average. On the second sheet (Evaluation Summary) I want to calculate the overall course average per instructor, per month. Although this is not exactly the solution that was suggested to me by some very helpful peolpe (I mean that) I finally found a function which will calculate the averages I need, and if the calculation returns a "#DIV/0!" error, to display the cell as blank. What I now need to do is add in the date selection component, so that evaluation averages are only calculated for a specific month, e.g.; = 1-Sept-08 and <= 30-Sep-08. I have tried to do this with dismal results. Can anyone help me with the correct syntax? The following is the calculation as it currently works: =IF(ISERROR(SUMPRODUCT(--('Evaluation Detail'!$F$7:$F$500="Linda Sgabellone"),'Evaluation Detail'!$Q$7:$Q$500)/COUNTIF('Evaluation Detail'!$F$7:$F$500,"Linda Sgabellone")),"",SUMPRODUCT(--('Evaluation Detail'!$F$7:$F$500="Linda Sgabellone"),'Evaluation Detail'!$Q$7:$Q$500)/COUNTIF('Evaluation Detail'!$F$7:$F$500,"Linda Sgabellone")) Thank you for all of your help - :) -- LPS |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
New Twist on Old Question-Averages
SUMPRODUCT can be used for conditional summing and for conditional
counting, (with more than one condition, unlike COUNTIF), so you can set up the conditions that you mention. I would favour having a cell in which you can enter a date like 1st Sept 2008 (although the day doesn't matter), and then your condition will be: (TEXT(D$7:D$500,"mmm-yy")=TEXT(cell,"mmm-yy")) to check for the month and year. Unfortunately I have to go out now, but I'll complete this later (if no-one else does). Hope this helps for now, Pete On Sep 25, 6:55*pm, LPS wrote: Refresher: *My Excel 2000 workbook has two sheets, the first (Evaluation Detail) contains details about course evalutions per instructor. *Column A is Course Name, B and C are Course Numbers, D is Course Date, E is Training Provider, F is Instuctor Name, G thru P are evaluation scores and Q is Evaluation Average. On the second sheet (Evaluation Summary) I want to calculate the overall course average per instructor, per month. Although this is not exactly the solution that was suggested to me by some very helpful peolpe (I mean that) I finally found a function which will calculate the averages I need, and if the calculation returns a "#DIV/0!" error, to display the cell as blank. *What I now need to do is add in the date selection component, so that evaluation averages are only calculated for a specific month, e.g.; = 1-Sept-08 and <= 30-Sep-08. *I have tried to do this with dismal results. *Can anyone help me with the correct syntax? *The following is the calculation as it currently works: =IF(ISERROR(SUMPRODUCT(--('Evaluation Detail'!$F$7:$F$500="Linda Sgabellone"),'Evaluation Detail'!$Q$7:$Q$500)/COUNTIF('Evaluation Detail'!$F$7:$F$500,"Linda Sgabellone")),"",SUMPRODUCT(--('Evaluation Detail'!$F$7:$F$500="Linda Sgabellone"),'Evaluation Detail'!$Q$7:$Q$500)/COUNTIF('Evaluation Detail'!$F$7:$F$500,"Linda Sgabellone")) Thank you for all of your help - :) -- LPS |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
New Twist on Old Question-Averages
Okay, try this:
=IF(ISERROR(SUMPRODUCT(--('Evaluation Detail'!$F$7:$F$500=A1),-- (TEXT('Evaluation Detail'D$7:D$500,"mmm-yy")=TEXT(B1,"mmm- yy")),'Evaluation Detail'!$Q$7:$Q$500)/SUMPRODUCT(--('Evaluation Detail'!$F$7:$F$500=A1),--(TEXT('Evaluation Detail'D$7:D$500,"mmm- yy")=TEXT(B1,"mmm-yy")))),"",SUMPRODUCT(--('Evaluation Detail'!$F$7:$F $500=A1),--(TEXT('Evaluation Detail'D$7:D$500,"mmm-yy")=TEXT(B1,"mmm- yy")),'Evaluation Detail'!$Q$7:$Q$500)/SUMPRODUCT(--('Evaluation Detail'!$F$7:$F$500=A1),--(TEXT('Evaluation Detail'D$7:D$500,"mmm- yy")=TEXT(B1,"mmm-yy")))) where A1 is used to enter the name of the person of interest (eg "Linda Sgabellone", without the quotes), and B1 is the date I mentioned earlier, eg 1st Sept 2008. An alternative would be to use AVERAGE(IF ... as an array formula, but I don't have time now to see if that will be significantly shorter. Hope this helps. Pete On Sep 25, 8:34*pm, Pete_UK wrote: SUMPRODUCT can be used for conditional summing and for conditional counting, (with more than one condition, unlike COUNTIF), so you can set up the conditions that you mention. I would favour having a cell in which you can enter a date like 1st Sept 2008 (although the day doesn't matter), and then your condition will be: (TEXT(D$7:D$500,"mmm-yy")=TEXT(cell,"mmm-yy")) to check for the month and year. Unfortunately I have to go out now, but I'll complete this later (if no-one else does). Hope this helps for now, Pete On Sep 25, 6:55*pm, LPS wrote: Refresher: *My Excel 2000 workbook has two sheets, the first (Evaluation Detail) contains details about course evalutions per instructor. *Column A is Course Name, B and C are Course Numbers, D is Course Date, E is Training Provider, F is Instuctor Name, G thru P are evaluation scores and Q is Evaluation Average. On the second sheet (Evaluation Summary) I want to calculate the overall course average per instructor, per month. Although this is not exactly the solution that was suggested to me by some very helpful peolpe (I mean that) I finally found a function which will calculate the averages I need, and if the calculation returns a "#DIV/0!" error, to display the cell as blank. *What I now need to do is add in the date selection component, so that evaluation averages are only calculated for a specific month, e.g.; = 1-Sept-08 and <= 30-Sep-08. *I have tried to do this with dismal results. *Can anyone help me with the correct syntax? *The following is the calculation as it currently works: =IF(ISERROR(SUMPRODUCT(--('Evaluation Detail'!$F$7:$F$500="Linda Sgabellone"),'Evaluation Detail'!$Q$7:$Q$500)/COUNTIF('Evaluation Detail'!$F$7:$F$500,"Linda Sgabellone")),"",SUMPRODUCT(--('Evaluation Detail'!$F$7:$F$500="Linda Sgabellone"),'Evaluation Detail'!$Q$7:$Q$500)/COUNTIF('Evaluation Detail'!$F$7:$F$500,"Linda Sgabellone")) Thank you for all of your help - :) -- LPS- Hide quoted text - - Show quoted text - |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
New Twist on Old Question-Averages
Hi Pete. I can see you have put a lot of thought into this for me and I
really appreciate it. I will have to play with this to see if I (a) understand it and (b) can make it work. Thanks so much. I will post a note to let you (and others who may be interested in a similar solution) know if I was successful. Cheers, -- LPS "Pete_UK" wrote: Okay, try this: =IF(ISERROR(SUMPRODUCT(--('Evaluation Detail'!$F$7:$F$500=A1),-- (TEXT('Evaluation Detail'D$7:D$500,"mmm-yy")=TEXT(B1,"mmm- yy")),'Evaluation Detail'!$Q$7:$Q$500)/SUMPRODUCT(--('Evaluation Detail'!$F$7:$F$500=A1),--(TEXT('Evaluation Detail'D$7:D$500,"mmm- yy")=TEXT(B1,"mmm-yy")))),"",SUMPRODUCT(--('Evaluation Detail'!$F$7:$F $500=A1),--(TEXT('Evaluation Detail'D$7:D$500,"mmm-yy")=TEXT(B1,"mmm- yy")),'Evaluation Detail'!$Q$7:$Q$500)/SUMPRODUCT(--('Evaluation Detail'!$F$7:$F$500=A1),--(TEXT('Evaluation Detail'D$7:D$500,"mmm- yy")=TEXT(B1,"mmm-yy")))) where A1 is used to enter the name of the person of interest (eg "Linda Sgabellone", without the quotes), and B1 is the date I mentioned earlier, eg 1st Sept 2008. An alternative would be to use AVERAGE(IF ... as an array formula, but I don't have time now to see if that will be significantly shorter. Hope this helps. Pete On Sep 25, 8:34 pm, Pete_UK wrote: SUMPRODUCT can be used for conditional summing and for conditional counting, (with more than one condition, unlike COUNTIF), so you can set up the conditions that you mention. I would favour having a cell in which you can enter a date like 1st Sept 2008 (although the day doesn't matter), and then your condition will be: (TEXT(D$7:D$500,"mmm-yy")=TEXT(cell,"mmm-yy")) to check for the month and year. Unfortunately I have to go out now, but I'll complete this later (if no-one else does). Hope this helps for now, Pete On Sep 25, 6:55 pm, LPS wrote: Refresher: My Excel 2000 workbook has two sheets, the first (Evaluation Detail) contains details about course evalutions per instructor. Column A is Course Name, B and C are Course Numbers, D is Course Date, E is Training Provider, F is Instuctor Name, G thru P are evaluation scores and Q is Evaluation Average. On the second sheet (Evaluation Summary) I want to calculate the overall course average per instructor, per month. Although this is not exactly the solution that was suggested to me by some very helpful peolpe (I mean that) I finally found a function which will calculate the averages I need, and if the calculation returns a "#DIV/0!" error, to display the cell as blank. What I now need to do is add in the date selection component, so that evaluation averages are only calculated for a specific month, e.g.; = 1-Sept-08 and <= 30-Sep-08. I have tried to do this with dismal results. Can anyone help me with the correct syntax? The following is the calculation as it currently works: =IF(ISERROR(SUMPRODUCT(--('Evaluation Detail'!$F$7:$F$500="Linda Sgabellone"),'Evaluation Detail'!$Q$7:$Q$500)/COUNTIF('Evaluation Detail'!$F$7:$F$500,"Linda Sgabellone")),"",SUMPRODUCT(--('Evaluation Detail'!$F$7:$F$500="Linda Sgabellone"),'Evaluation Detail'!$Q$7:$Q$500)/COUNTIF('Evaluation Detail'!$F$7:$F$500,"Linda Sgabellone")) Thank you for all of your help - :) -- LPS- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
New Twist on Old Question-Averages
Okay, feedback is always appreciated.
Pete On Sep 26, 6:27*pm, LPS wrote: Hi Pete. *I can see you have put a lot of thought into this for me and I really appreciate it. *I will have to play with this to see if I (a) understand it and (b) can make it work. Thanks so much. *I will post a note to let you (and others who may be interested in a similar solution) know if I was successful. Cheers, -- LPS "Pete_UK" wrote: Okay, try this: =IF(ISERROR(SUMPRODUCT(--('Evaluation Detail'!$F$7:$F$500=A1),-- (TEXT('Evaluation Detail'D$7:D$500,"mmm-yy")=TEXT(B1,"mmm- yy")),'Evaluation Detail'!$Q$7:$Q$500)/SUMPRODUCT(--('Evaluation Detail'!$F$7:$F$500=A1),--(TEXT('Evaluation Detail'D$7:D$500,"mmm- yy")=TEXT(B1,"mmm-yy")))),"",SUMPRODUCT(--('Evaluation Detail'!$F$7:$F $500=A1),--(TEXT('Evaluation Detail'D$7:D$500,"mmm-yy")=TEXT(B1,"mmm- yy")),'Evaluation Detail'!$Q$7:$Q$500)/SUMPRODUCT(--('Evaluation Detail'!$F$7:$F$500=A1),--(TEXT('Evaluation Detail'D$7:D$500,"mmm- yy")=TEXT(B1,"mmm-yy")))) where A1 is used to enter the name of the person of interest (eg "Linda Sgabellone", without the quotes), and B1 is the date I mentioned earlier, eg 1st Sept 2008. An alternative would be to use AVERAGE(IF ... *as an array formula, but I don't have time now to see if that will be significantly shorter. Hope this helps. Pete On Sep 25, 8:34 pm, Pete_UK wrote: SUMPRODUCT can be used for conditional summing and for conditional counting, (with more than one condition, unlike COUNTIF), so you can set up the conditions that you mention. I would favour having a cell in which you can enter a date like 1st Sept 2008 (although the day doesn't matter), and then your condition will be: (TEXT(D$7:D$500,"mmm-yy")=TEXT(cell,"mmm-yy")) to check for the month and year. Unfortunately I have to go out now, but I'll complete this later (if no-one else does). Hope this helps for now, Pete On Sep 25, 6:55 pm, LPS wrote: Refresher: *My Excel 2000 workbook has two sheets, the first (Evaluation Detail) contains details about course evalutions per instructor. *Column A is Course Name, B and C are Course Numbers, D is Course Date, E is Training Provider, F is Instuctor Name, G thru P are evaluation scores and Q is Evaluation Average. On the second sheet (Evaluation Summary) I want to calculate the overall course average per instructor, per month. Although this is not exactly the solution that was suggested to me by some very helpful peolpe (I mean that) I finally found a function which will calculate the averages I need, and if the calculation returns a "#DIV/0!" error, to display the cell as blank. *What I now need to do is add in the date selection component, so that evaluation averages are only calculated for a specific month, e.g.; = 1-Sept-08 and <= 30-Sep-08. *I have tried to do this with dismal results. *Can anyone help me with the correct syntax? *The following is the calculation as it currently works: =IF(ISERROR(SUMPRODUCT(--('Evaluation Detail'!$F$7:$F$500="Linda Sgabellone"),'Evaluation Detail'!$Q$7:$Q$500)/COUNTIF('Evaluation Detail'!$F$7:$F$500,"Linda Sgabellone")),"",SUMPRODUCT(--('Evaluation Detail'!$F$7:$F$500="Linda Sgabellone"),'Evaluation Detail'!$Q$7:$Q$500)/COUNTIF('Evaluation Detail'!$F$7:$F$500,"Linda Sgabellone")) Thank you for all of your help - :) -- LPS- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Subtotal Question with a twist | Excel Discussion (Misc queries) | |||
| I have a question on AVerages | New Users to Excel | |||
| Calculating averages excluding outliers...a question | Excel Worksheet Functions | |||
| Calculating basic averages...a question | Excel Worksheet Functions | |||
| A new twist to the validation drop-down width question. | Excel Discussion (Misc queries) | |||
Linear Mode
