|
Can you AVERAGE IF and not null?
I got the average already, thanks to you... but is there any way that i can
put a date range on the formula? something like i can only count the number of very satisfied ratings this september? can this be possible? =SUMPRODUCT(--(Data!A2:A100=B2),--(Data!B2:B100=C3),--(A1:A31=[Date.xls]Date!A1:A31)) is this possible? Thank you. "T. Valko" wrote: =COUNT(IF((Data!A2:A100=B2)*(Data!B2:B100=C3),Dat a!B2:B100)) Ok, I'm assuming you want the count of "very satisfied" for a particular person. =SUMPRODUCT(--(Data!A2:A100=B2),--(Data!B2:B100=C3)) If you're using Excel 2007: =COUNTIFS(Data!A2:A100,B2,Data!B2:B100,C3) I'm not sure about your average. *Exactly* what do you want to average? -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Thank you so much for this.. A follow up question, im trying to get the number and average of very satisfied comments of a lis of students.. ex. This is the Data Worksheet A B mike Very Satisfied Kris Very Satisfied Kris Satisfied Mike Satisfied Mike Very Satisfied Mike Not Satisfied Tamy Satisfied Tamy Satisfied and this is my formula : This is a cell from a different Worksheet D3 = Very Satisfied =COUNT(IF((Data!A2:A100=B2)*(Data!B2:B100=C3),Data !B2:B100)) Thank you so much! "T. Valko" wrote: For the count: Data in the range A2:A7... C2 = satisified =COUNTIF(A2:A7,C2) For the percentage: Assuming the count formula is in D2... =D2/COUNTA(A2:A7) Format as Percentage -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi Again, Got a question again.. How can i count text or words? Ex. in column A, i got texts that is equal to "Very Satisfied" and "Satisfied" and others. A Very Satisfied Satisfied Not Satisfied Very Satisfied Very Satisfied Satisfied How can i count the cells that contains "Very Satisfied"? and is there any way for me to get the Average of "Very Satisfied" against the total number of data that is in column A thank you. :) "T. Valko" wrote: I see that you're using the MS web interface so in the list on the left side select Excel Programming. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... ahh ok, thank you so much.. and do happen to know where can go i that forum? do u know the link to that furom? thanks. =) "T. Valko" wrote: Hmmm.... I'm not sure. You might be able to do that with an event macro but I don't know how to do it. Try posting this question in the programming forum. If A1 contains a formula make sure you note that in your question. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi, got a Question again... is there any formula that can automatically create a comment in a cell? ex. A1 = 80% and can we have a comment on that, that automatically that says "Passed" Thanks. "T. Valko" wrote: I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,"")) You can't use entire columns as range references in array formulas in Excel 2003. Use a smaller specific range. Try it like this (array entered**) =AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi, I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,"")) Im trying to get the AVERAGE of this and its giving me a #VALUE! error.. $A2 is the name that is suppose to be equal in the Data! worksheet and B$1 is the date that is suppose to be equal in the Data! worksheet. Im trying to use AND in IF for me to have two logical test.. Can you help me with this? Thank you so much! "JE McGimpsey" wrote: One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45)) In article , "Spottkitty" wrote: =AVERAGE(IF($A$3:$A$45="Photo",H3:H45)) This is the array I'm using to try to determine an average. Problem...if the field is blank it's counting it as zero and lowering the results. It figured 79% when it should have been 94%. 94% was returned using the simple average formula. I'm guessing I need to nest something to not count nulls? Help!!! |
Can you AVERAGE IF and not null?
To include a date range..
Data!A2:A100 = names Data!B2:B100 = response Data!C2:C100 = dates These are the criteria: B2 = some name = Mike C2 = some response = very satisfied D2 = start date = 9/1/2009 E2 = end date = 9/30/2009 =SUMPRODUCT(--(Data!A2:A100=B2),--(Data!B2:B100=C2),--(Data!C2:C100=D2),--(Data!C2:C100<=E2)) -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... I got the average already, thanks to you... but is there any way that i can put a date range on the formula? something like i can only count the number of very satisfied ratings this september? can this be possible? =SUMPRODUCT(--(Data!A2:A100=B2),--(Data!B2:B100=C3),--(A1:A31=[Date.xls]Date!A1:A31)) is this possible? Thank you. "T. Valko" wrote: =COUNT(IF((Data!A2:A100=B2)*(Data!B2:B100=C3),Dat a!B2:B100)) Ok, I'm assuming you want the count of "very satisfied" for a particular person. =SUMPRODUCT(--(Data!A2:A100=B2),--(Data!B2:B100=C3)) If you're using Excel 2007: =COUNTIFS(Data!A2:A100,B2,Data!B2:B100,C3) I'm not sure about your average. *Exactly* what do you want to average? -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Thank you so much for this.. A follow up question, im trying to get the number and average of very satisfied comments of a lis of students.. ex. This is the Data Worksheet A B mike Very Satisfied Kris Very Satisfied Kris Satisfied Mike Satisfied Mike Very Satisfied Mike Not Satisfied Tamy Satisfied Tamy Satisfied and this is my formula : This is a cell from a different Worksheet D3 = Very Satisfied =COUNT(IF((Data!A2:A100=B2)*(Data!B2:B100=C3),Data !B2:B100)) Thank you so much! "T. Valko" wrote: For the count: Data in the range A2:A7... C2 = satisified =COUNTIF(A2:A7,C2) For the percentage: Assuming the count formula is in D2... =D2/COUNTA(A2:A7) Format as Percentage -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi Again, Got a question again.. How can i count text or words? Ex. in column A, i got texts that is equal to "Very Satisfied" and "Satisfied" and others. A Very Satisfied Satisfied Not Satisfied Very Satisfied Very Satisfied Satisfied How can i count the cells that contains "Very Satisfied"? and is there any way for me to get the Average of "Very Satisfied" against the total number of data that is in column A thank you. :) "T. Valko" wrote: I see that you're using the MS web interface so in the list on the left side select Excel Programming. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... ahh ok, thank you so much.. and do happen to know where can go i that forum? do u know the link to that furom? thanks. =) "T. Valko" wrote: Hmmm.... I'm not sure. You might be able to do that with an event macro but I don't know how to do it. Try posting this question in the programming forum. If A1 contains a formula make sure you note that in your question. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi, got a Question again... is there any formula that can automatically create a comment in a cell? ex. A1 = 80% and can we have a comment on that, that automatically that says "Passed" Thanks. "T. Valko" wrote: I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,"")) You can't use entire columns as range references in array formulas in Excel 2003. Use a smaller specific range. Try it like this (array entered**) =AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi, I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,"")) Im trying to get the AVERAGE of this and its giving me a #VALUE! error.. $A2 is the name that is suppose to be equal in the Data! worksheet and B$1 is the date that is suppose to be equal in the Data! worksheet. Im trying to use AND in IF for me to have two logical test.. Can you help me with this? Thank you so much! "JE McGimpsey" wrote: One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45)) In article , "Spottkitty" wrote: =AVERAGE(IF($A$3:$A$45="Photo",H3:H45)) This is the array I'm using to try to determine an average. Problem...if the field is blank it's counting it as zero and lowering the results. It figured 79% when it should have been 94%. 94% was returned using the simple average formula. I'm guessing I need to nest something to not count nulls? Help!!! |
Can you AVERAGE IF and not null?
Thank you so much! everything is working now. Cheers!
"T. Valko" wrote: To include a date range.. Data!A2:A100 = names Data!B2:B100 = response Data!C2:C100 = dates These are the criteria: B2 = some name = Mike C2 = some response = very satisfied D2 = start date = 9/1/2009 E2 = end date = 9/30/2009 =SUMPRODUCT(--(Data!A2:A100=B2),--(Data!B2:B100=C2),--(Data!C2:C100=D2),--(Data!C2:C100<=E2)) -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... I got the average already, thanks to you... but is there any way that i can put a date range on the formula? something like i can only count the number of very satisfied ratings this september? can this be possible? =SUMPRODUCT(--(Data!A2:A100=B2),--(Data!B2:B100=C3),--(A1:A31=[Date.xls]Date!A1:A31)) is this possible? Thank you. "T. Valko" wrote: =COUNT(IF((Data!A2:A100=B2)*(Data!B2:B100=C3),Dat a!B2:B100)) Ok, I'm assuming you want the count of "very satisfied" for a particular person. =SUMPRODUCT(--(Data!A2:A100=B2),--(Data!B2:B100=C3)) If you're using Excel 2007: =COUNTIFS(Data!A2:A100,B2,Data!B2:B100,C3) I'm not sure about your average. *Exactly* what do you want to average? -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Thank you so much for this.. A follow up question, im trying to get the number and average of very satisfied comments of a lis of students.. ex. This is the Data Worksheet A B mike Very Satisfied Kris Very Satisfied Kris Satisfied Mike Satisfied Mike Very Satisfied Mike Not Satisfied Tamy Satisfied Tamy Satisfied and this is my formula : This is a cell from a different Worksheet D3 = Very Satisfied =COUNT(IF((Data!A2:A100=B2)*(Data!B2:B100=C3),Data !B2:B100)) Thank you so much! "T. Valko" wrote: For the count: Data in the range A2:A7... C2 = satisified =COUNTIF(A2:A7,C2) For the percentage: Assuming the count formula is in D2... =D2/COUNTA(A2:A7) Format as Percentage -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi Again, Got a question again.. How can i count text or words? Ex. in column A, i got texts that is equal to "Very Satisfied" and "Satisfied" and others. A Very Satisfied Satisfied Not Satisfied Very Satisfied Very Satisfied Satisfied How can i count the cells that contains "Very Satisfied"? and is there any way for me to get the Average of "Very Satisfied" against the total number of data that is in column A thank you. :) "T. Valko" wrote: I see that you're using the MS web interface so in the list on the left side select Excel Programming. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... ahh ok, thank you so much.. and do happen to know where can go i that forum? do u know the link to that furom? thanks. =) "T. Valko" wrote: Hmmm.... I'm not sure. You might be able to do that with an event macro but I don't know how to do it. Try posting this question in the programming forum. If A1 contains a formula make sure you note that in your question. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi, got a Question again... is there any formula that can automatically create a comment in a cell? ex. A1 = 80% and can we have a comment on that, that automatically that says "Passed" Thanks. "T. Valko" wrote: I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,"")) You can't use entire columns as range references in array formulas in Excel 2003. Use a smaller specific range. Try it like this (array entered**) =AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi, I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,"")) Im trying to get the AVERAGE of this and its giving me a #VALUE! error.. $A2 is the name that is suppose to be equal in the Data! worksheet and B$1 is the date that is suppose to be equal in the Data! worksheet. Im trying to use AND in IF for me to have two logical test.. Can you help me with this? Thank you so much! "JE McGimpsey" wrote: One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45)) In article , "Spottkitty" wrote: =AVERAGE(IF($A$3:$A$45="Photo",H3:H45)) This is the array I'm using to try to determine an average. Problem...if the field is blank it's counting it as zero and lowering the results. It figured 79% when it should have been 94%. 94% was returned using the simple average formula. I'm guessing I need to nest something to not count nulls? Help!!! |
Can you AVERAGE IF and not null?
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Thank you so much! everything is working now. Cheers! "T. Valko" wrote: To include a date range.. Data!A2:A100 = names Data!B2:B100 = response Data!C2:C100 = dates These are the criteria: B2 = some name = Mike C2 = some response = very satisfied D2 = start date = 9/1/2009 E2 = end date = 9/30/2009 =SUMPRODUCT(--(Data!A2:A100=B2),--(Data!B2:B100=C2),--(Data!C2:C100=D2),--(Data!C2:C100<=E2)) -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... I got the average already, thanks to you... but is there any way that i can put a date range on the formula? something like i can only count the number of very satisfied ratings this september? can this be possible? =SUMPRODUCT(--(Data!A2:A100=B2),--(Data!B2:B100=C3),--(A1:A31=[Date.xls]Date!A1:A31)) is this possible? Thank you. "T. Valko" wrote: =COUNT(IF((Data!A2:A100=B2)*(Data!B2:B100=C3),Dat a!B2:B100)) Ok, I'm assuming you want the count of "very satisfied" for a particular person. =SUMPRODUCT(--(Data!A2:A100=B2),--(Data!B2:B100=C3)) If you're using Excel 2007: =COUNTIFS(Data!A2:A100,B2,Data!B2:B100,C3) I'm not sure about your average. *Exactly* what do you want to average? -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Thank you so much for this.. A follow up question, im trying to get the number and average of very satisfied comments of a lis of students.. ex. This is the Data Worksheet A B mike Very Satisfied Kris Very Satisfied Kris Satisfied Mike Satisfied Mike Very Satisfied Mike Not Satisfied Tamy Satisfied Tamy Satisfied and this is my formula : This is a cell from a different Worksheet D3 = Very Satisfied =COUNT(IF((Data!A2:A100=B2)*(Data!B2:B100=C3),Data !B2:B100)) Thank you so much! "T. Valko" wrote: For the count: Data in the range A2:A7... C2 = satisified =COUNTIF(A2:A7,C2) For the percentage: Assuming the count formula is in D2... =D2/COUNTA(A2:A7) Format as Percentage -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi Again, Got a question again.. How can i count text or words? Ex. in column A, i got texts that is equal to "Very Satisfied" and "Satisfied" and others. A Very Satisfied Satisfied Not Satisfied Very Satisfied Very Satisfied Satisfied How can i count the cells that contains "Very Satisfied"? and is there any way for me to get the Average of "Very Satisfied" against the total number of data that is in column A thank you. :) "T. Valko" wrote: I see that you're using the MS web interface so in the list on the left side select Excel Programming. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... ahh ok, thank you so much.. and do happen to know where can go i that forum? do u know the link to that furom? thanks. =) "T. Valko" wrote: Hmmm.... I'm not sure. You might be able to do that with an event macro but I don't know how to do it. Try posting this question in the programming forum. If A1 contains a formula make sure you note that in your question. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi, got a Question again... is there any formula that can automatically create a comment in a cell? ex. A1 = 80% and can we have a comment on that, that automatically that says "Passed" Thanks. "T. Valko" wrote: I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,"")) You can't use entire columns as range references in array formulas in Excel 2003. Use a smaller specific range. Try it like this (array entered**) =AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi, I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,"")) Im trying to get the AVERAGE of this and its giving me a #VALUE! error.. $A2 is the name that is suppose to be equal in the Data! worksheet and B$1 is the date that is suppose to be equal in the Data! worksheet. Im trying to use AND in IF for me to have two logical test.. Can you help me with this? Thank you so much! "JE McGimpsey" wrote: One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45)) In article , "Spottkitty" wrote: =AVERAGE(IF($A$3:$A$45="Photo",H3:H45)) This is the array I'm using to try to determine an average. Problem...if the field is blank it's counting it as zero and lowering the results. It figured 79% when it should have been 94%. 94% was returned using the simple average formula. I'm guessing I need to nest something to not count nulls? Help!!! |
Can you AVERAGE IF and not null?
Hi!
It's me again :) I would like to ask for your help again.. How can i get the data from 1 worksheet to another? Sheet 1 A B C Date Name ACD March 1 Mike 34 March 1 John 100 March 1 James 75 March 2 John 80 In Sheet 2 How can I lookup or display the data from sheet 1 to sheet to sheet 2? ex. How can i display the ACD of John for March 1? A B C Date NAME ACD March 1 John ? What would be my formula in C3 for me to display the ACD of john for march 1? Thank you so much for your help! =) "T. Valko" wrote: I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,"")) You can't use entire columns as range references in array formulas in Excel 2003. Use a smaller specific range. Try it like this (array entered**) =AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi, I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,"")) Im trying to get the AVERAGE of this and its giving me a #VALUE! error.. $A2 is the name that is suppose to be equal in the Data! worksheet and B$1 is the date that is suppose to be equal in the Data! worksheet. Im trying to use AND in IF for me to have two logical test.. Can you help me with this? Thank you so much! "JE McGimpsey" wrote: One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45)) In article , "Spottkitty" wrote: =AVERAGE(IF($A$3:$A$45="Photo",H3:H45)) This is the array I'm using to try to determine an average. Problem...if the field is blank it's counting it as zero and lowering the results. It figured 79% when it should have been 94%. 94% was returned using the simple average formula. I'm guessing I need to nest something to not count nulls? Help!!! |
Can you AVERAGE IF and not null?
Try this array formula
=INDEX(Sheet1!$C:$C,MATCH(1,(Sheet1!$A$2:$A$200=A2 )*(Sheet1!$B$2:$B$200=B2),0)) -- HTH Bob "Hijosdelongi" wrote in message ... Hi! It's me again :) I would like to ask for your help again.. How can i get the data from 1 worksheet to another? Sheet 1 A B C Date Name ACD March 1 Mike 34 March 1 John 100 March 1 James 75 March 2 John 80 In Sheet 2 How can I lookup or display the data from sheet 1 to sheet to sheet 2? ex. How can i display the ACD of John for March 1? A B C Date NAME ACD March 1 John ? What would be my formula in C3 for me to display the ACD of john for march 1? Thank you so much for your help! =) "T. Valko" wrote: I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,"")) You can't use entire columns as range references in array formulas in Excel 2003. Use a smaller specific range. Try it like this (array entered**) =AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi, I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,"")) Im trying to get the AVERAGE of this and its giving me a #VALUE! error.. $A2 is the name that is suppose to be equal in the Data! worksheet and B$1 is the date that is suppose to be equal in the Data! worksheet. Im trying to use AND in IF for me to have two logical test.. Can you help me with this? Thank you so much! "JE McGimpsey" wrote: One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45)) In article , "Spottkitty" wrote: =AVERAGE(IF($A$3:$A$45="Photo",H3:H45)) This is the array I'm using to try to determine an average. Problem...if the field is blank it's counting it as zero and lowering the results. It figured 79% when it should have been 94%. 94% was returned using the simple average formula. I'm guessing I need to nest something to not count nulls? Help!!! |
Can you AVERAGE IF and not null?
Thanks a lot Bob, it worked great!
=) "Bob Phillips" wrote: Try this array formula =INDEX(Sheet1!$C:$C,MATCH(1,(Sheet1!$A$2:$A$200=A2 )*(Sheet1!$B$2:$B$200=B2),0)) -- HTH Bob "Hijosdelongi" wrote in message ... Hi! It's me again :) I would like to ask for your help again.. How can i get the data from 1 worksheet to another? Sheet 1 A B C Date Name ACD March 1 Mike 34 March 1 John 100 March 1 James 75 March 2 John 80 In Sheet 2 How can I lookup or display the data from sheet 1 to sheet to sheet 2? ex. How can i display the ACD of John for March 1? A B C Date NAME ACD March 1 John ? What would be my formula in C3 for me to display the ACD of john for march 1? Thank you so much for your help! =) "T. Valko" wrote: I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,"")) You can't use entire columns as range references in array formulas in Excel 2003. Use a smaller specific range. Try it like this (array entered**) =AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi, I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,"")) Im trying to get the AVERAGE of this and its giving me a #VALUE! error.. $A2 is the name that is suppose to be equal in the Data! worksheet and B$1 is the date that is suppose to be equal in the Data! worksheet. Im trying to use AND in IF for me to have two logical test.. Can you help me with this? Thank you so much! "JE McGimpsey" wrote: One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45)) In article , "Spottkitty" wrote: =AVERAGE(IF($A$3:$A$45="Photo",H3:H45)) This is the array I'm using to try to determine an average. Problem...if the field is blank it's counting it as zero and lowering the results. It figured 79% when it should have been 94%. 94% was returned using the simple average formula. I'm guessing I need to nest something to not count nulls? Help!!! . |
Can you AVERAGE IF and not null?
Hi There!
I Need your help again. I have This John VS 100 Mike VD -100 Mike VS 100 John VS 100 John VD -100 John S 0 I would like to compute the score of John, for him only This is the Formula : ( VS + ( VD )) / Total number of Surveys So its like this (200 + ( -100 )) / 4 Thanks, Hijosdelongi "T. Valko" wrote: I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,"")) You can't use entire columns as range references in array formulas in Excel 2003. Use a smaller specific range. Try it like this (array entered**) =AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi, I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,"")) Im trying to get the AVERAGE of this and its giving me a #VALUE! error.. $A2 is the name that is suppose to be equal in the Data! worksheet and B$1 is the date that is suppose to be equal in the Data! worksheet. Im trying to use AND in IF for me to have two logical test.. Can you help me with this? Thank you so much! "JE McGimpsey" wrote: One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45)) In article , "Spottkitty" wrote: =AVERAGE(IF($A$3:$A$45="Photo",H3:H45)) This is the array I'm using to try to determine an average. Problem...if the field is blank it's counting it as zero and lowering the results. It figured 79% when it should have been 94%. 94% was returned using the simple average formula. I'm guessing I need to nest something to not count nulls? Help!!! |
Can you AVERAGE IF and not null?
Try this...
=SUMPRODUCT(--(A2:A7="John"),--(ISNUMBER(MATCH(B2:B7,{"VS","VD"},0))),C2:C7)/COUNTIF(A2:A7,"John") Better to use cells to hold the criteria. E2 = John F2 = VS G2 = VD =SUMPRODUCT(--(A2:A7=E2),--(ISNUMBER(MATCH(B2:B7,F2:G2,0))),C2:C7)/COUNTIF(A2:A7,E2) -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi There! I Need your help again. I have This John VS 100 Mike VD -100 Mike VS 100 John VS 100 John VD -100 John S 0 I would like to compute the score of John, for him only This is the Formula : ( VS + ( VD )) / Total number of Surveys So its like this (200 + ( -100 )) / 4 Thanks, Hijosdelongi "T. Valko" wrote: I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,"")) You can't use entire columns as range references in array formulas in Excel 2003. Use a smaller specific range. Try it like this (array entered**) =AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi, I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,"")) Im trying to get the AVERAGE of this and its giving me a #VALUE! error.. $A2 is the name that is suppose to be equal in the Data! worksheet and B$1 is the date that is suppose to be equal in the Data! worksheet. Im trying to use AND in IF for me to have two logical test.. Can you help me with this? Thank you so much! "JE McGimpsey" wrote: One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45)) In article , "Spottkitty" wrote: =AVERAGE(IF($A$3:$A$45="Photo",H3:H45)) This is the array I'm using to try to determine an average. Problem...if the field is blank it's counting it as zero and lowering the results. It figured 79% when it should have been 94%. 94% was returned using the simple average formula. I'm guessing I need to nest something to not count nulls? Help!!! |
Can you AVERAGE IF and not null?
THank you so much! :)
"T. Valko" wrote: Try this... =SUMPRODUCT(--(A2:A7="John"),--(ISNUMBER(MATCH(B2:B7,{"VS","VD"},0))),C2:C7)/COUNTIF(A2:A7,"John") Better to use cells to hold the criteria. E2 = John F2 = VS G2 = VD =SUMPRODUCT(--(A2:A7=E2),--(ISNUMBER(MATCH(B2:B7,F2:G2,0))),C2:C7)/COUNTIF(A2:A7,E2) -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi There! I Need your help again. I have This John VS 100 Mike VD -100 Mike VS 100 John VS 100 John VD -100 John S 0 I would like to compute the score of John, for him only This is the Formula : ( VS + ( VD )) / Total number of Surveys So its like this (200 + ( -100 )) / 4 Thanks, Hijosdelongi "T. Valko" wrote: I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,"")) You can't use entire columns as range references in array formulas in Excel 2003. Use a smaller specific range. Try it like this (array entered**) =AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi, I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,"")) Im trying to get the AVERAGE of this and its giving me a #VALUE! error.. $A2 is the name that is suppose to be equal in the Data! worksheet and B$1 is the date that is suppose to be equal in the Data! worksheet. Im trying to use AND in IF for me to have two logical test.. Can you help me with this? Thank you so much! "JE McGimpsey" wrote: One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45)) In article , "Spottkitty" wrote: =AVERAGE(IF($A$3:$A$45="Photo",H3:H45)) This is the array I'm using to try to determine an average. Problem...if the field is blank it's counting it as zero and lowering the results. It figured 79% when it should have been 94%. 94% was returned using the simple average formula. I'm guessing I need to nest something to not count nulls? Help!!! . |
Can you AVERAGE IF and not null?
Hi,
Why is that its giving me a 0 value? The survey its actually like this : VS = 100 S = 0 NS = 0 VD = -100 D = -100 So if John has John VS John VD John D John S John NS Mike D Mike VS Mike VS the formula again is (VS + ( D + VD ))/Total number of surveys So its like this (100 + ( -200 )) / 5 So the answer for this should be -20 Thanks again for your help :) "T. Valko" wrote: Try this... =SUMPRODUCT(--(A2:A7="John"),--(ISNUMBER(MATCH(B2:B7,{"VS","VD"},0))),C2:C7)/COUNTIF(A2:A7,"John") Better to use cells to hold the criteria. E2 = John F2 = VS G2 = VD =SUMPRODUCT(--(A2:A7=E2),--(ISNUMBER(MATCH(B2:B7,F2:G2,0))),C2:C7)/COUNTIF(A2:A7,E2) -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi There! I Need your help again. I have This John VS 100 Mike VD -100 Mike VS 100 John VS 100 John VD -100 John S 0 I would like to compute the score of John, for him only This is the Formula : ( VS + ( VD )) / Total number of Surveys So its like this (200 + ( -100 )) / 4 Thanks, Hijosdelongi "T. Valko" wrote: I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,"")) You can't use entire columns as range references in array formulas in Excel 2003. Use a smaller specific range. Try it like this (array entered**) =AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi, I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,"")) Im trying to get the AVERAGE of this and its giving me a #VALUE! error.. $A2 is the name that is suppose to be equal in the Data! worksheet and B$1 is the date that is suppose to be equal in the Data! worksheet. Im trying to use AND in IF for me to have two logical test.. Can you help me with this? Thank you so much! "JE McGimpsey" wrote: One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45)) In article , "Spottkitty" wrote: =AVERAGE(IF($A$3:$A$45="Photo",H3:H45)) This is the array I'm using to try to determine an average. Problem...if the field is blank it's counting it as zero and lowering the results. It figured 79% when it should have been 94%. 94% was returned using the simple average formula. I'm guessing I need to nest something to not count nulls? Help!!! . |
Can you AVERAGE IF and not null?
The survey its actually like this
So, you have a separate table with the code values? Ok, rearange your code table so that it's sorted in ascending order like this: D...-100 NS...0 S...0 VD...-100 VS...100 Assume that table is in the range A1:B5 Your list of names and codes is in the range D1:E8. Array entered** : =AVERAGE(IF(D1:D8="John",LOOKUP(E1:E8,A1:B5))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi, Why is that its giving me a 0 value? The survey its actually like this : VS = 100 S = 0 NS = 0 VD = -100 D = -100 So if John has John VS John VD John D John S John NS Mike D Mike VS Mike VS the formula again is (VS + ( D + VD ))/Total number of surveys So its like this (100 + ( -200 )) / 5 So the answer for this should be -20 Thanks again for your help :) "T. Valko" wrote: Try this... =SUMPRODUCT(--(A2:A7="John"),--(ISNUMBER(MATCH(B2:B7,{"VS","VD"},0))),C2:C7)/COUNTIF(A2:A7,"John") Better to use cells to hold the criteria. E2 = John F2 = VS G2 = VD =SUMPRODUCT(--(A2:A7=E2),--(ISNUMBER(MATCH(B2:B7,F2:G2,0))),C2:C7)/COUNTIF(A2:A7,E2) -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi There! I Need your help again. I have This John VS 100 Mike VD -100 Mike VS 100 John VS 100 John VD -100 John S 0 I would like to compute the score of John, for him only This is the Formula : ( VS + ( VD )) / Total number of Surveys So its like this (200 + ( -100 )) / 4 Thanks, Hijosdelongi "T. Valko" wrote: I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,"")) You can't use entire columns as range references in array formulas in Excel 2003. Use a smaller specific range. Try it like this (array entered**) =AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi, I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,"")) Im trying to get the AVERAGE of this and its giving me a #VALUE! error.. $A2 is the name that is suppose to be equal in the Data! worksheet and B$1 is the date that is suppose to be equal in the Data! worksheet. Im trying to use AND in IF for me to have two logical test.. Can you help me with this? Thank you so much! "JE McGimpsey" wrote: One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45)) In article , "Spottkitty" wrote: =AVERAGE(IF($A$3:$A$45="Photo",H3:H45)) This is the array I'm using to try to determine an average. Problem...if the field is blank it's counting it as zero and lowering the results. It figured 79% when it should have been 94%. 94% was returned using the simple average formula. I'm guessing I need to nest something to not count nulls? Help!!! . |
Can you AVERAGE IF and not null?
Thanks, let me try that :)
"T. Valko" wrote: The survey its actually like this So, you have a separate table with the code values? Ok, rearange your code table so that it's sorted in ascending order like this: D...-100 NS...0 S...0 VD...-100 VS...100 Assume that table is in the range A1:B5 Your list of names and codes is in the range D1:E8. Array entered** : =AVERAGE(IF(D1:D8="John",LOOKUP(E1:E8,A1:B5))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi, Why is that its giving me a 0 value? The survey its actually like this : VS = 100 S = 0 NS = 0 VD = -100 D = -100 So if John has John VS John VD John D John S John NS Mike D Mike VS Mike VS the formula again is (VS + ( D + VD ))/Total number of surveys So its like this (100 + ( -200 )) / 5 So the answer for this should be -20 Thanks again for your help :) "T. Valko" wrote: Try this... =SUMPRODUCT(--(A2:A7="John"),--(ISNUMBER(MATCH(B2:B7,{"VS","VD"},0))),C2:C7)/COUNTIF(A2:A7,"John") Better to use cells to hold the criteria. E2 = John F2 = VS G2 = VD =SUMPRODUCT(--(A2:A7=E2),--(ISNUMBER(MATCH(B2:B7,F2:G2,0))),C2:C7)/COUNTIF(A2:A7,E2) -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi There! I Need your help again. I have This John VS 100 Mike VD -100 Mike VS 100 John VS 100 John VD -100 John S 0 I would like to compute the score of John, for him only This is the Formula : ( VS + ( VD )) / Total number of Surveys So its like this (200 + ( -100 )) / 4 Thanks, Hijosdelongi "T. Valko" wrote: I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,"")) You can't use entire columns as range references in array formulas in Excel 2003. Use a smaller specific range. Try it like this (array entered**) =AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi, I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,"")) Im trying to get the AVERAGE of this and its giving me a #VALUE! error.. $A2 is the name that is suppose to be equal in the Data! worksheet and B$1 is the date that is suppose to be equal in the Data! worksheet. Im trying to use AND in IF for me to have two logical test.. Can you help me with this? Thank you so much! "JE McGimpsey" wrote: One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45)) In article , "Spottkitty" wrote: =AVERAGE(IF($A$3:$A$45="Photo",H3:H45)) This is the array I'm using to try to determine an average. Problem...if the field is blank it's counting it as zero and lowering the results. It figured 79% when it should have been 94%. 94% was returned using the simple average formula. I'm guessing I need to nest something to not count nulls? Help!!! . . |
| All times are GMT +1. The time now is 06:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com