Home |
Search |
Today's Posts |
|
#1
![]() |
|||
|
|||
![]()
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!!! |
#2
![]() |
|||
|
|||
![]()
Thank you SOOOOOOOOO Much!!!! Worked like a charm!
"JE McGimpsey" wrote: One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45)) |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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!!! |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
You can't use the entire column for array formulas in xl2003 and below.
(Remember to use ctrl-shift-enter, too) Hijosdelongi wrote: 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!!! -- Dave Peterson |
#5
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
ic, but is the =AVERAGE(IF(AND( correct?
Thanks "Dave Peterson" wrote: You can't use the entire column for array formulas in xl2003 and below. (Remember to use ctrl-shift-enter, too) Hijosdelongi wrote: 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!!! -- Dave Peterson |
#6
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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!!! |
#7
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
EXCELENT! It worked!!! and how can I hide the #DIV/0! if there are still no
scores for that they.. 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!!! |
#8
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Try it like this:
Array entered. =IF(ISERROR(AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B 10=A2),Data!C1:C10))),"",AVERAGE(IF((Data!A1:A10=B 1)*(Data!B1:B10=A2),Data!C1:C10))) -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... EXCELENT! It worked!!! and how can I hide the #DIV/0! if there are still no scores for that they.. 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!!! |
#9
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Does ISERROR works in Excel 2003?
Thanks :) "T. Valko" wrote: Try it like this: Array entered. =IF(ISERROR(AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B 10=A2),Data!C1:C10))),"",AVERAGE(IF((Data!A1:A10=B 1)*(Data!B1:B10=A2),Data!C1:C10))) -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... EXCELENT! It worked!!! and how can I hide the #DIV/0! if there are still no scores for that they.. 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!!! |
#10
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
I got the answer already, thank you so much!!!
=) "T. Valko" wrote: Try it like this: Array entered. =IF(ISERROR(AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B 10=A2),Data!C1:C10))),"",AVERAGE(IF((Data!A1:A10=B 1)*(Data!B1:B10=A2),Data!C1:C10))) -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... EXCELENT! It worked!!! and how can I hide the #DIV/0! if there are still no scores for that they.. 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!!! |
#11
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
You're welcome!
-- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... I got the answer already, thank you so much!!! =) "T. Valko" wrote: Try it like this: Array entered. =IF(ISERROR(AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B 10=A2),Data!C1:C10))),"",AVERAGE(IF((Data!A1:A10=B 1)*(Data!B1:B10=A2),Data!C1:C10))) -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... EXCELENT! It worked!!! and how can I hide the #DIV/0! if there are still no scores for that they.. 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!!! |
#12
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hi,
I have a Question.. is VLOOKUP plus IF possible? This is my fomula.. =VLOOKUP(IF((Data!A1:A1000=A1)*(Data!B1:B1000=D1), .... is my logical tests or conditions correct? and how will i put the VLOOKUP codes? Can you help me with this.. THank you so much "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!!! |
#13
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hi T. Valko,
How are you? Got a Question again... im trying to get a data from the database using VLOOKUP and why is that even though there is no value in the database it still displays the 0 value? And can you teach mo how to get a value from the database even though theres no value in it? ex. A1 = "null value or no value" how will display a value that is equivalent to a text or number even though theres no value in the database? 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!!! |
#14
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
You'll have to post the formula.
-- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi T. Valko, How are you? Got a Question again... im trying to get a data from the database using VLOOKUP and why is that even though there is no value in the database it still displays the 0 value? And can you teach mo how to get a value from the database even though theres no value in it? ex. A1 = "null value or no value" how will display a value that is equivalent to a text or number even though theres no value in the database? 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!!! |
#15
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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!!! |
#16
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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!!! |
#17
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
ahh ok, thanks dude =)
"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!!! |
#18
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
You're welcome!
-- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... ahh ok, thanks dude =) "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!!! |
#19
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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!!! |
#20
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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!!! |
#21
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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!!! |
#22
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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!!! |
#23
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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!!! |
#24
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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!!! |
#25
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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!!! |
#26
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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!!! |
#27
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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!!! . |
#28
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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!!! |
#29
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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!!! |
#30
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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!!! . |
#31
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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!!! . |
#32
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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!!! . |
#33
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
My situation is similar but I haven't been able to customize this to work
like I thought I should be able to. I have one cell in multiple tabs I that want to include in the average, as long as they <0. If anyone of them <0, then I do not want that particular cell to be factored into the result because it skews the average (because it's a month that hasn't occured yet so the data is 0). Here's my (nonworking) formula if anyone can help: =average(if(jundata!b2,juldata!b2,augdata!b2,sepda ta!b2,octdata!b2,novdata!b2,decdatab2<""),jundata !b2,juldata!b2,augdata!b2,sepdata!b2,octdata!b2,no vdata!b2,decdata!b2) "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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Will there ever be any negative numbers?
-- Biff Microsoft Excel MVP "klic33" wrote in message ... My situation is similar but I haven't been able to customize this to work like I thought I should be able to. I have one cell in multiple tabs I that want to include in the average, as long as they <0. If anyone of them <0, then I do not want that particular cell to be factored into the result because it skews the average (because it's a month that hasn't occured yet so the data is 0). Here's my (nonworking) formula if anyone can help: =average(if(jundata!b2,juldata!b2,augdata!b2,sepda ta!b2,octdata!b2,novdata!b2,decdatab2<""),jundata !b2,juldata!b2,augdata!b2,sepdata!b2,octdata!b2,no vdata!b2,decdata!b2) "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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
no, nothing less than zero
"T. Valko" wrote: Will there ever be any negative numbers? -- Biff Microsoft Excel MVP "klic33" wrote in message ... My situation is similar but I haven't been able to customize this to work like I thought I should be able to. I have one cell in multiple tabs I that want to include in the average, as long as they <0. If anyone of them <0, then I do not want that particular cell to be factored into the result because it skews the average (because it's a month that hasn't occured yet so the data is 0). Here's my (nonworking) formula if anyone can help: =average(if(jundata!b2,juldata!b2,augdata!b2,sepda ta!b2,octdata!b2,novdata!b2,decdatab2<""),jundata !b2,juldata!b2,augdata!b2,sepdata!b2,octdata!b2,no vdata!b2,decdata!b2) "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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Try this:
Create these named formulas... InsertNameDefine Name: SumSheets Refers to: =SUMIF(INDIRECT(TEXT(30*{6,7,8,9,10,11,12},"mmm\da ta")&"!B2"),"0") Name: CountSheets Refers to: =COUNTIF(INDIRECT(TEXT(30*{6,7,8,9,10,11,12},"mmm\ data")&"!B2"),"0") Then, you average formula is: =SUMPRODUCT(SumSheets)/SUMPRODUCT(CountSheets) Explanation: Excel doesn't support *conditional* averaging across multiple sheets so we need to trick it into doing so. We can't use the AVERAGE function in this case. Since an average is the sum divided by the count that's what we're doing with the above formula(s). If you want to take the "easy" way out on this, on each sheet in the same cell enter a formula like this: =IF(B20,B2,"") Let's assume those formulas are in cell B3. Then, you can use the AVAERAGE function like this: =AVERAGE(jundata:decdata!B3) -- Biff Microsoft Excel MVP "klic33" wrote in message ... no, nothing less than zero "T. Valko" wrote: Will there ever be any negative numbers? -- Biff Microsoft Excel MVP "klic33" wrote in message ... My situation is similar but I haven't been able to customize this to work like I thought I should be able to. I have one cell in multiple tabs I that want to include in the average, as long as they <0. If anyone of them <0, then I do not want that particular cell to be factored into the result because it skews the average (because it's a month that hasn't occured yet so the data is 0). Here's my (nonworking) formula if anyone can help: =average(if(jundata!b2,juldata!b2,augdata!b2,sepda ta!b2,octdata!b2,novdata!b2,decdatab2<""),jundata !b2,juldata!b2,augdata!b2,sepdata!b2,octdata!b2,no vdata!b2,decdata!b2) "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!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|