Home |
Search |
Today's Posts |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you AVERAGE IF and not null?
Thanks a lot Bob, it worked great!
=) "Bob Phillips" wrote: Try this array formula =INDEX(Sheet1!$C:$C,MATCH(1,(Sheet1!$A$2:$A$200=A2 )*(Sheet1!$B$2:$B$200=B2),0)) -- HTH Bob "Hijosdelongi" wrote in message ... Hi! It's me again :) I would like to ask for your help again.. How can i get the data from 1 worksheet to another? Sheet 1 A B C Date Name ACD March 1 Mike 34 March 1 John 100 March 1 James 75 March 2 John 80 In Sheet 2 How can I lookup or display the data from sheet 1 to sheet to sheet 2? ex. How can i display the ACD of John for March 1? A B C Date NAME ACD March 1 John ? What would be my formula in C3 for me to display the ACD of john for march 1? Thank you so much for your help! =) "T. Valko" wrote: I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,"")) You can't use entire columns as range references in array formulas in Excel 2003. Use a smaller specific range. Try it like this (array entered**) =AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Hi, I have a question for Excel 2003 =AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,"")) Im trying to get the AVERAGE of this and its giving me a #VALUE! error.. $A2 is the name that is suppose to be equal in the Data! worksheet and B$1 is the date that is suppose to be equal in the Data! worksheet. Im trying to use AND in IF for me to have two logical test.. Can you help me with this? Thank you so much! "JE McGimpsey" wrote: One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45)) In article , "Spottkitty" wrote: =AVERAGE(IF($A$3:$A$45="Photo",H3:H45)) This is the array I'm using to try to determine an average. Problem...if the field is blank it's counting it as zero and lowering the results. It figured 79% when it should have been 94%. 94% was returned using the simple average formula. I'm guessing I need to nest something to not count nulls? Help!!! . |