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 |
#51
|
|||
|
|||
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(D18="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!!! . |
#52
|
|||
|
|||
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(D18="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!!! . . |
Thread Tools | |
Display Modes | |
|
|