If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#31
|
|||
|
|||
Can you AVERAGE IF and not null?
This actually works but is there any way it can calculate how many more 100's
a student needs to have for him/her to reach 72..? ex, jorge alrealy has an average of 70 out of 12 exams, so how many more 100's he needs to get for him to get a 72+ average.. Thank you. hijosdelongi "T. Valko" wrote: Try this: =IF(B1=72,"",72-B1) Copy down as needed. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi, need some help again.. I have this project and i'm calculating the average of my students.. I have this table.. A B C 1 Mike 60 ? 2 Jorge 70 ? 3 Stan 65 ? I would like to ask whats the formula to calculate how much more a student needs to have for him/her to get 72? 72 is the passing score, and i would like to ask how much he needs to reach 72. Possible score for a student to have is between 0-100. Thanks for you usual help. hijosdelongi "T. Valko" wrote: No, a formula can't do that. If you want it to be "semi-automatic" you'd need a macro. You can do it with a few clicks of a mouse... Remove the error trap from your formula and let the errors generate. Select column E Goto the menu EditGo ToSpecial Select: Formulas and uncheck everything *except* Errors OK That will select all the cells in col E that contain errors Goto the menu EditDelete Select: Entire Row OK -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... hi Again, Got a question again, is there any formula that can automatically erase or remove an entire row if a specific cell doesnt have any value or an error value to it??? ex. I have this vlookup value in column E =IF(ISERROR(VLOOKUP(A1:A5,Sheet1!B1:E100,3,FALSE)) ,"",VLOOKUP(A1:A5,Sheet1!B1:E100,3,FALSE))) Is there anyway that i can delete or remove the entire row 4 if theres no returned value or an error value??? A B C D E 1 x g e e sharon 2 x as vf v sharon 3 y g h j david 4 f b a r 5 p r e f dexter Thank you.. "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!!! |
#32
|
|||
|
|||
Can you AVERAGE IF and not null?
Sorry, I'm not sure I know how to do that.
-- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... This actually works but is there any way it can calculate how many more 100's a student needs to have for him/her to reach 72..? ex, jorge alrealy has an average of 70 out of 12 exams, so how many more 100's he needs to get for him to get a 72+ average.. Thank you. hijosdelongi "T. Valko" wrote: Try this: =IF(B1=72,"",72-B1) Copy down as needed. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi, need some help again.. I have this project and i'm calculating the average of my students.. I have this table.. A B C 1 Mike 60 ? 2 Jorge 70 ? 3 Stan 65 ? I would like to ask whats the formula to calculate how much more a student needs to have for him/her to get 72? 72 is the passing score, and i would like to ask how much he needs to reach 72. Possible score for a student to have is between 0-100. Thanks for you usual help. hijosdelongi "T. Valko" wrote: No, a formula can't do that. If you want it to be "semi-automatic" you'd need a macro. You can do it with a few clicks of a mouse... Remove the error trap from your formula and let the errors generate. Select column E Goto the menu EditGo ToSpecial Select: Formulas and uncheck everything *except* Errors OK That will select all the cells in col E that contain errors Goto the menu EditDelete Select: Entire Row OK -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... hi Again, Got a question again, is there any formula that can automatically erase or remove an entire row if a specific cell doesnt have any value or an error value to it??? ex. I have this vlookup value in column E =IF(ISERROR(VLOOKUP(A1:A5,Sheet1!B1:E100,3,FALSE)) ,"",VLOOKUP(A1:A5,Sheet1!B1:E100,3,FALSE))) Is there anyway that i can delete or remove the entire row 4 if theres no returned value or an error value??? A B C D E 1 x g e e sharon 2 x as vf v sharon 3 y g h j david 4 f b a r 5 p r e f dexter Thank you.. "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!!! |
#33
|
|||
|
|||
Can you AVERAGE IF and not null?
no problem dude, youve been so much help.. thank you. =)
"T. Valko" wrote: Sorry, I'm not sure I know how to do that. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... This actually works but is there any way it can calculate how many more 100's a student needs to have for him/her to reach 72..? ex, jorge alrealy has an average of 70 out of 12 exams, so how many more 100's he needs to get for him to get a 72+ average.. Thank you. hijosdelongi "T. Valko" wrote: Try this: =IF(B1=72,"",72-B1) Copy down as needed. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi, need some help again.. I have this project and i'm calculating the average of my students.. I have this table.. A B C 1 Mike 60 ? 2 Jorge 70 ? 3 Stan 65 ? I would like to ask whats the formula to calculate how much more a student needs to have for him/her to get 72? 72 is the passing score, and i would like to ask how much he needs to reach 72. Possible score for a student to have is between 0-100. Thanks for you usual help. hijosdelongi "T. Valko" wrote: No, a formula can't do that. If you want it to be "semi-automatic" you'd need a macro. You can do it with a few clicks of a mouse... Remove the error trap from your formula and let the errors generate. Select column E Goto the menu EditGo ToSpecial Select: Formulas and uncheck everything *except* Errors OK That will select all the cells in col E that contain errors Goto the menu EditDelete Select: Entire Row OK -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... hi Again, Got a question again, is there any formula that can automatically erase or remove an entire row if a specific cell doesnt have any value or an error value to it??? ex. I have this vlookup value in column E =IF(ISERROR(VLOOKUP(A1:A5,Sheet1!B1:E100,3,FALSE)) ,"",VLOOKUP(A1:A5,Sheet1!B1:E100,3,FALSE))) Is there anyway that i can delete or remove the entire row 4 if theres no returned value or an error value??? A B C D E 1 x g e e sharon 2 x as vf v sharon 3 y g h j david 4 f b a r 5 p r e f dexter Thank you.. "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!!! |
#34
|
|||
|
|||
Can you AVERAGE IF and not null?
Hi, its me again..
If ever you find the answer to my last Question, please do message me.. thank you so much! hijosdelongi "T. Valko" wrote: Sorry, I'm not sure I know how to do that. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... This actually works but is there any way it can calculate how many more 100's a student needs to have for him/her to reach 72..? ex, jorge alrealy has an average of 70 out of 12 exams, so how many more 100's he needs to get for him to get a 72+ average.. Thank you. hijosdelongi "T. Valko" wrote: Try this: =IF(B1=72,"",72-B1) Copy down as needed. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi, need some help again.. I have this project and i'm calculating the average of my students.. I have this table.. A B C 1 Mike 60 ? 2 Jorge 70 ? 3 Stan 65 ? I would like to ask whats the formula to calculate how much more a student needs to have for him/her to get 72? 72 is the passing score, and i would like to ask how much he needs to reach 72. Possible score for a student to have is between 0-100. Thanks for you usual help. hijosdelongi "T. Valko" wrote: No, a formula can't do that. If you want it to be "semi-automatic" you'd need a macro. You can do it with a few clicks of a mouse... Remove the error trap from your formula and let the errors generate. Select column E Goto the menu EditGo ToSpecial Select: Formulas and uncheck everything *except* Errors OK That will select all the cells in col E that contain errors Goto the menu EditDelete Select: Entire Row OK -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... hi Again, Got a question again, is there any formula that can automatically erase or remove an entire row if a specific cell doesnt have any value or an error value to it??? ex. I have this vlookup value in column E =IF(ISERROR(VLOOKUP(A1:A5,Sheet1!B1:E100,3,FALSE)) ,"",VLOOKUP(A1:A5,Sheet1!B1:E100,3,FALSE))) Is there anyway that i can delete or remove the entire row 4 if theres no returned value or an error value??? A B C D E 1 x g e e sharon 2 x as vf v sharon 3 y g h j david 4 f b a r 5 p r e f dexter Thank you.. "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!!! |
#35
|
|||
|
|||
Can you AVERAGE IF and not null?
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!!! |
#36
|
|||
|
|||
Can you AVERAGE IF and not null?
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!!! |
#37
|
|||
|
|||
Can you AVERAGE IF and not null?
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!!! |
#38
|
|||
|
|||
Can you AVERAGE IF and not null?
=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!!! |
#39
|
|||
|
|||
Can you AVERAGE IF and not null?
for that i want to get the average of number of very satisfied rating versus
the non very satisfied rating of a single person.. ex. for mike, he has 4 surveys and 2 out of 4 are very satisfied, so clearly its 50% right... thats what i mean for the average, i dont know how to formulate that. 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!!! |
#40
|
|||
|
|||
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!!! |
Thread Tools | |
Display Modes | |
|
|