Problem with Array Formula

**TheRobsterUK** · 08-26-2010, 07:16 AM

Hi All,

I am currently having a problem with an array formula which I think needs to use the AND function (and this may be what is causing it not to work).

I have a spreadsheet containing monitoring data for household water usage and the data set consists of about 1000 houses of various types (different occupancy rates and type of house, e.g. detached, semi-detached etc). There are three columns: the average monthly water use. household occupancy (1-6) and household type (detached = D, semi-detached= S, Terraced = T and Other = 0).

Basically I need to work out the average by house occupancy and house type. So for instance what is the average monthly water usage of all detached houses with 1 occupant?

I created an array formula that works if I only want to know the average of, for example, water use in 1 person households (regardless of the type of house). E.g.

{=AVERAGE(IF(DI2:DI1113=1,DL2:DL1113))}

Where column DI is the occupancy rates and column DL is the average monthly water usage.

However whenever I try and filter for house occupancy and house type by incorporating the AND function, I just get a value of zero returned. Here is the formula I am currently using:

{=AVERAGE(IF(AND(DI2:DI1113=1, DJ2:DJ1113="D"), DL2:DL1113))}

Where column DJ is the house type. So I would read the above as: For all cells in the range DI2:DI1113 and DJ2:DJ1113, check to see which have the combination of "1" and "D" and then work out the corresponding averages from the range DL2:DL1113.

However it doesn't work and I just get zero (I have checked and there are cells that have non-zero values that meet the criteria so it definitely shouldn't be zero). All I can think is that the AND statement is making it not work but I don't know why.

Thanks in advance for any help.

Regards
Rob

**daddylonglegs** · 08-26-2010, 07:22 AM

Hello Rob, typically you can't use AND in these sorts of formulas because AND returns a single result rather than the required array, try either

=AVERAGE(IF(DI2:DI1113=1,IF(DJ2:DJ1113="D",DL2:DL1113)))

or

=AVERAGE(IF((DI2:DI1113=1)*(DJ2:DJ1113="D"),DL2:DL1113))

both confirmed with CTRL+SHIFT+ENTER

I believe the former is more efficient

Note: if you have Excel 2007 then AVERAGEIFS is a better option

**martindwilson** · 08-26-2010, 07:26 AM

=average(if((di2:di1113=1)*(dj2:dj1113="d"), dl2:dl1113))

**TheRobsterUK** · 08-26-2010, 07:40 AM

That's great guys, thanks.

=AVERAGE(IF((DI2:DI1113=1)*(DJ2:DJ1113="D"),DL2:DL1113))

I used the first formula - I'm not sure why the above one would actually work! What is the effect of multiplying the two terms together? (Can't be numeric as one is a number and the other a character).

Cheers
Rob

**daddylonglegs** · 08-26-2010, 07:54 AM

The result of

(DI2:DI1113=1)

is an array of TRUE or FALSE values so when you multiply 2 such arrays TRUE is co-erced to 1 and FALSE zero so the multiplication gives you a new array of 1/0 values and you only get 1 when both conditions are TRUE for that row.....

In the IF function 1 is deemed to be TRUE (as is any non-zero number) and zero is FALSE. The overall effect should be the same as the other formula.