| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XL2000 - Average/If/And Functions
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. I have written an AVERAGE statement which calls an IF/AND statement (see below). It works but returns the same results for each instructor. When I manually calculate the averages per instructor per date,they are not the same. Can anyone advise as to why my function is not working. I tried making it an array function (CTRL+SHIFT+ENTER) but it caused the results to return "0" (and I don't really understand array functions - :( )! =AVERAGE(IF(AND('Evaluation Detail'!$D$7:$D$499=DATE(2008,9,1),'Evaluation Detail'!$D$7:$D$499<=DATE(2008,9,30)),IF('Evaluati on Detail'!$F$7:$F$499="Thaddeus Thomas",IF('Evaluation Detail'!$Q$7:$Q$499<0,'Evaluation Detail'!$Q$7:$Q$499,0)))) I greatly appreciate any and all help. -- LPS |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
XL2000 - Average/If/And Functions
P.S.: When I copy the function to another instructor's average cell, I do
change the name of the instructor. -- LPS "LPS" wrote: 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. I have written an AVERAGE statement which calls an IF/AND statement (see below). It works but returns the same results for each instructor. When I manually calculate the averages per instructor per date,they are not the same. Can anyone advise as to why my function is not working. I tried making it an array function (CTRL+SHIFT+ENTER) but it caused the results to return "0" (and I don't really understand array functions - :( )! =AVERAGE(IF(AND('Evaluation Detail'!$D$7:$D$499=DATE(2008,9,1),'Evaluation Detail'!$D$7:$D$499<=DATE(2008,9,30)),IF('Evaluati on Detail'!$F$7:$F$499="Thaddeus Thomas",IF('Evaluation Detail'!$Q$7:$Q$499<0,'Evaluation Detail'!$Q$7:$Q$499,0)))) I greatly appreciate any and all help. -- LPS |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
XL2000 - Average/If/And Functions
=AVERAGE(IF(('Evaluation Detail'!$D$7:$D$499=DATE(2008,9,1))
*('Evaluation Detail'!$D$7:$D$499<=DATE(2008,9,30)) *('Evaluation Detail'!$F$7:$F$499="Thaddeus Thomas") *('Evaluation Detail'!$Q$7:$Q$499<0),'Evaluation Detail'!$Q$7:$Q$499,0)) You MUST array enter it, it won't work otherwise. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "LPS" wrote in message ... 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. I have written an AVERAGE statement which calls an IF/AND statement (see below). It works but returns the same results for each instructor. When I manually calculate the averages per instructor per date,they are not the same. Can anyone advise as to why my function is not working. I tried making it an array function (CTRL+SHIFT+ENTER) but it caused the results to return "0" (and I don't really understand array functions - :( )! =AVERAGE(IF(AND('Evaluation Detail'!$D$7:$D$499=DATE(2008,9,1),'Evaluation Detail'!$D$7:$D$499<=DATE(2008,9,30)),IF('Evaluati on Detail'!$F$7:$F$499="Thaddeus Thomas",IF('Evaluation Detail'!$Q$7:$Q$499<0,'Evaluation Detail'!$Q$7:$Q$499,0)))) I greatly appreciate any and all help. -- LPS |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
XL2000 - Average/If/And Functions
Thank you for your suggestion, Bob... I tried it but it returns an incorrect
value and I do not understand what it is doing. Cannot I not just enter a formula which says, if the month is September and the instructor is Thaddeus Thomas, average his course evaluations? In an array function, what do the asterisk and the squiggly brackets mean/do? Are there any reference materials I can access? -- LPS "Bob Phillips" wrote: =AVERAGE(IF(('Evaluation Detail'!$D$7:$D$499=DATE(2008,9,1)) *('Evaluation Detail'!$D$7:$D$499<=DATE(2008,9,30)) *('Evaluation Detail'!$F$7:$F$499="Thaddeus Thomas") *('Evaluation Detail'!$Q$7:$Q$499<0),'Evaluation Detail'!$Q$7:$Q$499,0)) You MUST array enter it, it won't work otherwise. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "LPS" wrote in message ... 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. I have written an AVERAGE statement which calls an IF/AND statement (see below). It works but returns the same results for each instructor. When I manually calculate the averages per instructor per date,they are not the same. Can anyone advise as to why my function is not working. I tried making it an array function (CTRL+SHIFT+ENTER) but it caused the results to return "0" (and I don't really understand array functions - :( )! =AVERAGE(IF(AND('Evaluation Detail'!$D$7:$D$499=DATE(2008,9,1),'Evaluation Detail'!$D$7:$D$499<=DATE(2008,9,30)),IF('Evaluati on Detail'!$F$7:$F$499="Thaddeus Thomas",IF('Evaluation Detail'!$Q$7:$Q$499<0,'Evaluation Detail'!$Q$7:$Q$499,0)))) I greatly appreciate any and all help. -- LPS |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
XL2000 - Average/If/And Functions
That is exactly what that formula is doing. The * are effectively AND
statements here, Dates GT 1st Sep AND Dates <= 30th Sep AND instructors = "Thaddeus Thomas", by virtue of the way that the conditions are evaluated. Looking at it agian, the test for the numbere not 0 is superfluous, a number + 0 is the same number, so it is only (sic!) =AVERAGE(IF(('Evaluation Detail'!$D$7:$D$499=DATE(2008,9,1)) *('Evaluation Detail'!$D$7:$D$499<=DATE(2008,9,30)) *('Evaluation Detail'!$F$7:$F$499="Thaddeus Thomas"),'Evaluation Detail'!$Q$7:$Q$499,0)) The curly brackets are added by Excel when you do Ctrl-Shift_Enter, they are not added by you. As I said you MUST Ctrl-Shift_Enter the formula, and if you edit it, you must Ctrl-Shift_Enter again. It will return an incorrect answer if you just Enter it. -- __________________________________ HTH Bob "LPS" wrote in message ... Thank you for your suggestion, Bob... I tried it but it returns an incorrect value and I do not understand what it is doing. Cannot I not just enter a formula which says, if the month is September and the instructor is Thaddeus Thomas, average his course evaluations? In an array function, what do the asterisk and the squiggly brackets mean/do? Are there any reference materials I can access? -- LPS "Bob Phillips" wrote: =AVERAGE(IF(('Evaluation Detail'!$D$7:$D$499=DATE(2008,9,1)) *('Evaluation Detail'!$D$7:$D$499<=DATE(2008,9,30)) *('Evaluation Detail'!$F$7:$F$499="Thaddeus Thomas") *('Evaluation Detail'!$Q$7:$Q$499<0),'Evaluation Detail'!$Q$7:$Q$499,0)) You MUST array enter it, it won't work otherwise. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "LPS" wrote in message ... 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. I have written an AVERAGE statement which calls an IF/AND statement (see below). It works but returns the same results for each instructor. When I manually calculate the averages per instructor per date,they are not the same. Can anyone advise as to why my function is not working. I tried making it an array function (CTRL+SHIFT+ENTER) but it caused the results to return "0" (and I don't really understand array functions - :( )! =AVERAGE(IF(AND('Evaluation Detail'!$D$7:$D$499=DATE(2008,9,1),'Evaluation Detail'!$D$7:$D$499<=DATE(2008,9,30)),IF('Evaluati on Detail'!$F$7:$F$499="Thaddeus Thomas",IF('Evaluation Detail'!$Q$7:$Q$499<0,'Evaluation Detail'!$Q$7:$Q$499,0)))) I greatly appreciate any and all help. -- LPS |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
XL2000 - Average/If/And Functions
Hi again. Thank you again for trying to make it easy for me. I tried your
suggestion and even with using the array formula (ctrl+shift+enter), it gives incorrect results. I just don't get it. I was using the "number not equal to 0" as a way of eiliminating from the calcuation, cells that were blank, as zero values can sqew averages. I don't know what to try next.... any suggestions? -- LPS "Bob Phillips" wrote: That is exactly what that formula is doing. The * are effectively AND statements here, Dates GT 1st Sep AND Dates <= 30th Sep AND instructors = "Thaddeus Thomas", by virtue of the way that the conditions are evaluated. Looking at it agian, the test for the numbere not 0 is superfluous, a number + 0 is the same number, so it is only (sic!) =AVERAGE(IF(('Evaluation Detail'!$D$7:$D$499=DATE(2008,9,1)) *('Evaluation Detail'!$D$7:$D$499<=DATE(2008,9,30)) *('Evaluation Detail'!$F$7:$F$499="Thaddeus Thomas"),'Evaluation Detail'!$Q$7:$Q$499,0)) The curly brackets are added by Excel when you do Ctrl-Shift_Enter, they are not added by you. As I said you MUST Ctrl-Shift_Enter the formula, and if you edit it, you must Ctrl-Shift_Enter again. It will return an incorrect answer if you just Enter it. -- __________________________________ HTH Bob "LPS" wrote in message ... Thank you for your suggestion, Bob... I tried it but it returns an incorrect value and I do not understand what it is doing. Cannot I not just enter a formula which says, if the month is September and the instructor is Thaddeus Thomas, average his course evaluations? In an array function, what do the asterisk and the squiggly brackets mean/do? Are there any reference materials I can access? -- LPS "Bob Phillips" wrote: =AVERAGE(IF(('Evaluation Detail'!$D$7:$D$499=DATE(2008,9,1)) *('Evaluation Detail'!$D$7:$D$499<=DATE(2008,9,30)) *('Evaluation Detail'!$F$7:$F$499="Thaddeus Thomas") *('Evaluation Detail'!$Q$7:$Q$499<0),'Evaluation Detail'!$Q$7:$Q$499,0)) You MUST array enter it, it won't work otherwise. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "LPS" wrote in message ... 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. I have written an AVERAGE statement which calls an IF/AND statement (see below). It works but returns the same results for each instructor. When I manually calculate the averages per instructor per date,they are not the same. Can anyone advise as to why my function is not working. I tried making it an array function (CTRL+SHIFT+ENTER) but it caused the results to return "0" (and I don't really understand array functions - :( )! =AVERAGE(IF(AND('Evaluation Detail'!$D$7:$D$499=DATE(2008,9,1),'Evaluation Detail'!$D$7:$D$499<=DATE(2008,9,30)),IF('Evaluati on Detail'!$F$7:$F$499="Thaddeus Thomas",IF('Evaluation Detail'!$Q$7:$Q$499<0,'Evaluation Detail'!$Q$7:$Q$499,0)))) I greatly appreciate any and all help. -- LPS |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
XL2000 - Average/If/And Functions
I am using this function, --
=AVERAGE(IF(('Evaluation Detail'!$D$7:$D$499=DATE(2008,9,1)) *('Evaluation Detail'!$D$7:$D$499<=DATE(2008,9,30)) *('Evaluation Detail'!$F$7:$F$499="Thaddeus Thomas"),'Evaluation Detail'!$Q$7:$Q$499,0)) However, if is not working. When I copy it to another instructor's average cell, and change the instructor's name in the function, it still calculates the same result, regardless of whether or not the instructor's name even exists in the source data. LPS "LPS" wrote: Hi again. Thank you again for trying to make it easy for me. I tried your suggestion and even with using the array formula (ctrl+shift+enter), it gives incorrect results. I just don't get it. I was using the "number not equal to 0" as a way of eiliminating from the calcuation, cells that were blank, as zero values can sqew averages. I don't know what to try next.... any suggestions? -- LPS "Bob Phillips" wrote: That is exactly what that formula is doing. The * are effectively AND statements here, Dates GT 1st Sep AND Dates <= 30th Sep AND instructors = "Thaddeus Thomas", by virtue of the way that the conditions are evaluated. Looking at it agian, the test for the numbere not 0 is superfluous, a number + 0 is the same number, so it is only (sic!) =AVERAGE(IF(('Evaluation Detail'!$D$7:$D$499=DATE(2008,9,1)) *('Evaluation Detail'!$D$7:$D$499<=DATE(2008,9,30)) *('Evaluation Detail'!$F$7:$F$499="Thaddeus Thomas"),'Evaluation Detail'!$Q$7:$Q$499,0)) The curly brackets are added by Excel when you do Ctrl-Shift_Enter, they are not added by you. As I said you MUST Ctrl-Shift_Enter the formula, and if you edit it, you must Ctrl-Shift_Enter again. It will return an incorrect answer if you just Enter it. -- __________________________________ HTH Bob "LPS" wrote in message ... Thank you for your suggestion, Bob... I tried it but it returns an incorrect value and I do not understand what it is doing. Cannot I not just enter a formula which says, if the month is September and the instructor is Thaddeus Thomas, average his course evaluations? In an array function, what do the asterisk and the squiggly brackets mean/do? Are there any reference materials I can access? -- LPS "Bob Phillips" wrote: =AVERAGE(IF(('Evaluation Detail'!$D$7:$D$499=DATE(2008,9,1)) *('Evaluation Detail'!$D$7:$D$499<=DATE(2008,9,30)) *('Evaluation Detail'!$F$7:$F$499="Thaddeus Thomas") *('Evaluation Detail'!$Q$7:$Q$499<0),'Evaluation Detail'!$Q$7:$Q$499,0)) You MUST array enter it, it won't work otherwise. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "LPS" wrote in message ... 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. I have written an AVERAGE statement which calls an IF/AND statement (see below). It works but returns the same results for each instructor. When I manually calculate the averages per instructor per date,they are not the same. Can anyone advise as to why my function is not working. I tried making it an array function (CTRL+SHIFT+ENTER) but it caused the results to return "0" (and I don't really understand array functions - :( )! =AVERAGE(IF(AND('Evaluation Detail'!$D$7:$D$499=DATE(2008,9,1),'Evaluation Detail'!$D$7:$D$499<=DATE(2008,9,30)),IF('Evaluati on Detail'!$F$7:$F$499="Thaddeus Thomas",IF('Evaluation Detail'!$Q$7:$Q$499<0,'Evaluation Detail'!$Q$7:$Q$499,0)))) I greatly appreciate any and all help. -- LPS |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
XL2000 - Average/If/And Functions
There was a small bug in the formula that I didn't change from your
original, it should be =AVERAGE(IF(('Evaluation Detail'!$D$7:$D$499=DATE(2008,9,1)) *('Evaluation Detail'!$D$7:$D$499<=DATE(2008,9,30)) *('Evaluation Detail'!$F$7:$F$499="Thaddeus Thomas"),'Evaluation Detail'!$Q$7:$Q$499)) still array entered. What does this mean ... When I copy it to another instructor's average cell, and change the instructor's name in the function, -- __________________________________ HTH Bob "LPS" wrote in message ... I am using this function, -- =AVERAGE(IF(('Evaluation Detail'!$D$7:$D$499=DATE(2008,9,1)) *('Evaluation Detail'!$D$7:$D$499<=DATE(2008,9,30)) *('Evaluation Detail'!$F$7:$F$499="Thaddeus Thomas"),'Evaluation Detail'!$Q$7:$Q$499,0)) However, if is not working. When I copy it to another instructor's average cell, and change the instructor's name in the function, it still calculates the same result, regardless of whether or not the instructor's name even exists in the source data. LPS "LPS" wrote: Hi again. Thank you again for trying to make it easy for me. I tried your suggestion and even with using the array formula (ctrl+shift+enter), it gives incorrect results. I just don't get it. I was using the "number not equal to 0" as a way of eiliminating from the calcuation, cells that were blank, as zero values can sqew averages. I don't know what to try next.... any suggestions? -- LPS "Bob Phillips" wrote: That is exactly what that formula is doing. The * are effectively AND statements here, Dates GT 1st Sep AND Dates <= 30th Sep AND instructors = "Thaddeus Thomas", by virtue of the way that the conditions are evaluated. Looking at it agian, the test for the numbere not 0 is superfluous, a number + 0 is the same number, so it is only (sic!) =AVERAGE(IF(('Evaluation Detail'!$D$7:$D$499=DATE(2008,9,1)) *('Evaluation Detail'!$D$7:$D$499<=DATE(2008,9,30)) *('Evaluation Detail'!$F$7:$F$499="Thaddeus Thomas"),'Evaluation Detail'!$Q$7:$Q$499,0)) The curly brackets are added by Excel when you do Ctrl-Shift_Enter, they are not added by you. As I said you MUST Ctrl-Shift_Enter the formula, and if you edit it, you must Ctrl-Shift_Enter again. It will return an incorrect answer if you just Enter it. -- __________________________________ HTH Bob "LPS" wrote in message ... Thank you for your suggestion, Bob... I tried it but it returns an incorrect value and I do not understand what it is doing. Cannot I not just enter a formula which says, if the month is September and the instructor is Thaddeus Thomas, average his course evaluations? In an array function, what do the asterisk and the squiggly brackets mean/do? Are there any reference materials I can access? -- LPS "Bob Phillips" wrote: =AVERAGE(IF(('Evaluation Detail'!$D$7:$D$499=DATE(2008,9,1)) *('Evaluation Detail'!$D$7:$D$499<=DATE(2008,9,30)) *('Evaluation Detail'!$F$7:$F$499="Thaddeus Thomas") *('Evaluation Detail'!$Q$7:$Q$499<0),'Evaluation Detail'!$Q$7:$Q$499,0)) You MUST array enter it, it won't work otherwise. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "LPS" wrote in message ... 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. I have written an AVERAGE statement which calls an IF/AND statement (see below). It works but returns the same results for each instructor. When I manually calculate the averages per instructor per date,they are not the same. Can anyone advise as to why my function is not working. I tried making it an array function (CTRL+SHIFT+ENTER) but it caused the results to return "0" (and I don't really understand array functions - :( )! =AVERAGE(IF(AND('Evaluation Detail'!$D$7:$D$499=DATE(2008,9,1),'Evaluation Detail'!$D$7:$D$499<=DATE(2008,9,30)),IF('Evaluati on Detail'!$F$7:$F$499="Thaddeus Thomas",IF('Evaluation Detail'!$Q$7:$Q$499<0,'Evaluation Detail'!$Q$7:$Q$499,0)))) I greatly appreciate any and all help. -- LPS |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formula/functions for average and if functions | Excel Worksheet Functions | |||
| sumproduct and average functions | Excel Worksheet Functions | |||
| AVERAGE / OFFSET FUNCTIONS, TODAY.. | Excel Discussion (Misc queries) | |||
| Help with IF & Average Functions | Excel Worksheet Functions | |||
| MIN, MAX, AVERAGE functions | Excel Worksheet Functions | |||
Linear Mode
