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 |
#11
|
|||
|
|||
Can you AVERAGE IF and not null?
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
|
|||
|
|||
Can you AVERAGE IF and not null?
You're welcome!
-- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message news 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!!! |
#13
|
|||
|
|||
Can you AVERAGE IF and not null?
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!!! |
#14
|
|||
|
|||
Can you AVERAGE IF and not null?
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!!! |
#15
|
|||
|
|||
Can you AVERAGE IF and not null?
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!!! |
#16
|
|||
|
|||
Can you AVERAGE IF and not null?
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!!! |
#17
|
|||
|
|||
Can you AVERAGE IF and not null?
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!!! |
#18
|
|||
|
|||
Can you AVERAGE IF and not null?
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!!! |
#19
|
|||
|
|||
Can you AVERAGE IF and not null?
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!!! |
#20
|
|||
|
|||
Can you AVERAGE IF and not null?
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!!! |
Thread Tools | |
Display Modes | |
|
|