Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex if
This formula returns a "false" but should return a number: As an array.....
=IF(AND($I$2:$I$3536"",$H$2:$H$3536"",$H$2:$H$35 36=10/1/2008,$H$2:$H$353611/1/2008),AVERAGE($J$2:$J$3536,"")) Columns I and H are dates. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex if
It can return FALSE because of the end of the formula:
Change AVERAGE($J$2:$J$3536,"")) to AVERAGE($J$2:$J$3536),"") "PAL" wrote in message ... This formula returns a "false" but should return a number: As an array..... =IF(AND($I$2:$I$3536"",$H$2:$H$3536"",$H$2:$H$35 36=10/1/2008,$H$2:$H$353611/1/2008),AVERAGE($J$2:$J$3536,"")) Columns I and H are dates. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex if
Hi,
I think there's a few issues with the fromula Condition 1 & 2 $I$2:$I$3536"",$H$2:$H$3536"", Do you mean $I$2:$I$3536<"",$H$2:$H$3536<"", Conditions 2 & 3 $H$2:$H$3536=10/1/2008,$H$2:$H$353611/1/2008) Perhaps you mean $H$2:$H$3536=date(2008/10/1) Excel will interpret 10/1/2008 as 10 divided by 1 divided by 2008 which equals 0.00498 and not a date Argument 2 & 3 are contradictory. anything =1 Oct 2008 will catch a date 1 Nov 2008 so what is the point of the second date argument. Do you mean = 1 Oct 2008 < 1 Nov 2009 perhaps you could explain what it is your trying to achieve and someone will help. Mike "PAL" wrote: This formula returns a "false" but should return a number: As an array..... =IF(AND($I$2:$I$3536"",$H$2:$H$3536"",$H$2:$H$35 36=10/1/2008,$H$2:$H$353611/1/2008),AVERAGE($J$2:$J$3536,"")) Columns I and H are dates. Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex if
loking at it all again I think you mean this
=AVERAGE(IF(H2:H3536=DATE(2008,10,1),IF(I2:I3536< "",IF(I2:I3536<DATE(2008,11,1),J2:J3536,"")))) Mike "Mike H" wrote: Hi, I think there's a few issues with the fromula Condition 1 & 2 $I$2:$I$3536"",$H$2:$H$3536"", Do you mean $I$2:$I$3536<"",$H$2:$H$3536<"", Conditions 2 & 3 $H$2:$H$3536=10/1/2008,$H$2:$H$353611/1/2008) Perhaps you mean $H$2:$H$3536=date(2008/10/1) Excel will interpret 10/1/2008 as 10 divided by 1 divided by 2008 which equals 0.00498 and not a date Argument 2 & 3 are contradictory. anything =1 Oct 2008 will catch a date 1 Nov 2008 so what is the point of the second date argument. Do you mean = 1 Oct 2008 < 1 Nov 2009 perhaps you could explain what it is your trying to achieve and someone will help. Mike "PAL" wrote: This formula returns a "false" but should return a number: As an array..... =IF(AND($I$2:$I$3536"",$H$2:$H$3536"",$H$2:$H$35 36=10/1/2008,$H$2:$H$353611/1/2008),AVERAGE($J$2:$J$3536,"")) Columns I and H are dates. Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex if
A few points:
1 I have a feeling that AND doesn't work with arrays. 2 You are comparing column H with a very small number, 10 divided by 1 (sic) then divided by 2008. 3 You say that I and H are dates. If H is 11 Oct 08 it is bound to be = 10 Oct 08. Perhaps you intended the 11/1/08 comparison to be < rather than ? 4 You are trying to take the average of a range which includes column J and the null text string "", but of course the AVERAGE function ignores text strings, so there is no point in including it in the argument for AVERAGE. I guess that you may have intended the penultimate parenthesis to be before the preceding comma, so that the "" is not included in the AVERAGE but as the alternative outcome of the IF, but I think you probably wanted the AVERAGE to be the outer function and the IF to govern which rows of column J were to be included in the average? 5 You are testing for columns I and H being "", but if H or I is a date, it is a number, and a number will not be greater than the text string. .... and there may be other points. Guessing at what you might have wanted, perhaps: =AVERAGE(IF(($I$2:$I$3536<"")*($H$2:$H$3536<"")* ($H$2:$H$3536=DATE(2008,10,1))*($H$2:$H$3536<DATE (2008,11,1)),$J$2:$J$3536,""))as an array formula--David BiddulphPAL wrote: This formula returns a "false" but should return a number: As an array.....=IF(AND($I$2:$I$3536"",$H$2:$H$3536" ",$H$2:$H$3536=10/1/2008,$H$2:$H$353611/1/2008),AVERAGE($J$2:$J$3536,"")) Columns I and H are dates. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex If/Then | Excel Worksheet Functions | |||
Complex Look up | Excel Worksheet Functions | |||
Complex sum | Excel Discussion (Misc queries) | |||
Complex sum | Excel Discussion (Misc queries) | |||
Complex Index Match Help (or at least complex to me) | Excel Discussion (Misc queries) |