Do Array Formulas have to be populated!?

[genepaton]

Hi All,

I've written a couple of formulas for calculating averages in excel 2003 based on multiple criteria, but as the data is changing and sometimes not populated at all in these rows, its spitting out a #DIV/0! error.

Do Array Formula's require at least one cell populated?

And if so, is there a work around for this?

I need for it to populate a "0" answer if there is no data for it to work off.

Example formula:

{=AVERAGE(IF((Sheet1!B2:B65536="2")*(Sheet1!L2:L65536<>0),Sheet1!L2:L65536))/1440}

I tried working around it by including the second condition that the entry must be greater than 0, thinking that excel counted the blank cells as 0 value.

FYI Im working with time values in minutes, hence the /1440 and formatting the cell to hh:mm

 

[T. Valko]

Hi All,

I've written a couple of formulas for calculating averages in excel 2003 based on multiple criteria, but as the data is changing and sometimes not populated at all in these rows, its spitting out a #DIV/0! error.

Do Array Formula's require at least one cell populated?

And if so, is there a work around for this?

I need for it to populate a "0" answer if there is no data for it to work off.

Example formula:

{=AVERAGE(IF((Sheet1!B2:B65536="2")*(Sheet1!L2:L65536<>0),Sheet1!L2:L65536))/1440}

I tried working around it by including the second condition that the entry must be greater than 0, thinking that excel counted the blank cells as 0 value.

FYI Im working with time values in minutes, hence the /1440 and formatting the cell to hh:mm

Try it like this...

Still array entered:

=LOOKUP(1E100,CHOOSE({1,2},0,AVERAGE(IF(Sheet1!B2:B65536="2",IF(Sheet1!L2:L65536<>0,Sheet1!L2:L65536)))/1440))

Are you sure you want to quote "2"? When you quote numbers that turns them into TEXT strings.

 

[genepaton]

Hi T Valko,

Thanks! That works.
I need it to quote "2" as that column is only populated by 'classes' eg 1, 2 or 3 only.

Would there be a better way?

 

[T. Valko]

Hi T Valko,

Thanks! That works.
I need it to quote "2" as that column is only populated by 'classes' eg 1, 2 or 3 only.

Would there be a better way?

OK, if the formula works with the quoted "2" then let's not mess with it!

Thanks for the feedback!

 

[genepaton]

Ahhh, before you take off.

I just tried applying your formula to some other multiple criteria formulas i had, and they seem to be rejecting the arguments.
Maybe because they're quoted???

Here's the existing formulas i had, but again need to utilise yours so that is the column are all blank entries it gets returned as 0.

{=AVERAGE(IF((Sheet1!K10:K65536>=20)*(Sheet1!T10:T65536="ENROLLED (EXCL. AB CLASS)"),Sheet1!K10:K65536))/1440}

and so on for MEDIAN eg.

{=MEDIAN(IF((Sheet1!K10:K65536>=20)*(Sheet1!T10:T65536="ENROLLED (EXCL. AB CLASS)"),Sheet1!K10:K65536))/1440}

{=MEDIAN(IF((Sheet1!T10:T65536="ENROLLED (EXCL. AB CLASS)")*(Sheet1!M10:M65536>0),Sheet1!M10:M65536))/1440}

I tried yours but it rejects the "quoted" variable

{=LOOKUP(E1+100,CHOOSE({1,2},0,AVERAGE(IF(Sheet1!K10:K65536>=20,IF(Sheet1!T10:T65536="ENROLLED (EXCL. AB CLASS)",Sheet1!K10:K65536)))/1440)))}

 

[Aladin Akyurek]

Try to define BigNum first, referring to

=9.99999999999999E+307

or just to:

=9.99E+307

For formula expressions, expected to return a number, we can invoke...

=LOOKUP(BigNum,CHOOSE({1,2},0,formula expression))

to return a 0 whenever the formula expression would end up in an eroor value, like #DV/0!, etc.

Note that the formula expression determines whether you need to confirm such a formula with just enter or control+shift+enter.

For the particular formula you have, it becomes:

=LOOKUP(BigNum,CHOOSE({1,2},0,
   AVERAGE(IF((Sheet1!B2:B65536="2")*(Sheet1!L2:L65536<>0),
     Sheet1!L2:L65536))/1440))

which requires control+shift+enter,

 

[T. Valko]

Ahhh, before you take off.

I just tried applying your formula to some other multiple criteria formulas i had, and they seem to be rejecting the arguments.
Maybe because they're quoted???

Here's the existing formulas i had, but again need to utilise yours so that is the column are all blank entries it gets returned as 0.

{=AVERAGE(IF((Sheet1!K10:K65536>=20)*(Sheet1!T10:T65536="ENROLLED (EXCL. AB CLASS)"),Sheet1!K10:K65536))/1440}

and so on for MEDIAN eg.

{=MEDIAN(IF((Sheet1!K10:K65536>=20)*(Sheet1!T10:T65536="ENROLLED (EXCL. AB CLASS)"),Sheet1!K10:K65536))/1440}

{=MEDIAN(IF((Sheet1!T10:T65536="ENROLLED (EXCL. AB CLASS)")*(Sheet1!M10:M65536>0),Sheet1!M10:M65536))/1440}

I tried yours but it rejects the "quoted" variable

{=LOOKUP(E1+100,CHOOSE({1,2},0,AVERAGE(IF(Sheet1!K10:K65536>=20,IF(Sheet1!T10:T65536="ENROLLED (EXCL. AB CLASS)",Sheet1!K10:K65536)))/1440)))}

Not sure what you mean by "it rejects the "quoted" variable".

The lookup value should be 1E100 (which is scientific notation for a very large number).

There's also an extra closing ) at the very end of the formula.

=LOOKUP(1E100,CHOOSE({1,2},0,AVERAGE(IF(Sheet1!K10:K65536>=20,IF(Sheet1!T10:T65536="ENROLLED (EXCL. AB CLASS)",Sheet1!K10:K65536)))/1440))

I'm a big fan of using cells to hold the criteria:

• A1 = ENROLLED (EXCL. AB CLASS)

=LOOKUP(1E100,CHOOSE({1,2},0,AVERAGE(IF(Sheet1!K10:K65536>=20,IF(Sheet1!T10:T65536=A1,Sheet1!K10:K65536)))/1440))

 

[genepaton]

Hey T Valko,

Thanks for the pointer using cells to hold the criteria, makes things a lot neater.

Thanks for explaining that, your formula worked great and i was then able to adapt it to the others.
When i said "quoted" variable, i was referring to the criteria; being a quoted cell value, but the use of reference cells eliminated that anyway.

Hi Aladin,

Thanks for explaining the formula T Valko supplied, it gave me a better understanding on what it was doing.

 

[genepaton]

Hey T Valko,

I tried to PM you but you're not accepting, anyway we can get in touch?

 

[T. Valko]

Hey T Valko,

Thanks for the pointer using cells to hold the criteria, makes things a lot neater.

Thanks for explaining that, your formula worked great and i was then able to adapt it to the others.
When i said "quoted" variable, i was referring to the criteria; being a quoted cell value, but the use of reference cells eliminated that anyway.

Good deal. Thanks for the feedback!