Hi there,
When applying a built-in Excel function (such as AVERAGE) to an array, all FALSE elements are automatically excluded. For example, the formula:
=AVERAGE({2,4,FALSE})
is treated as being equivalent to
=AVERAGE({2,4})
and so both formulae produce the value 3, as expected.
However, the following formula:
=AVERAGE({2,4,FALSE}-{1,1,FALSE})
produces the value 1.33333, which (as far as I'm concerned) is unexpected. I would have expected the above formula to produce the value 2, since the FALSE element in the resulting internal array *should* be ignored, and the formula should be equivalent to
=AVERAGE({2,4}-{1,1})
It would appear that, upon performing the internal subtraction, Excel is replacing the FALSE element with the number zero. So if I'm understanding this correctly, Excel is essentially carrying out the following simplification:
=AVERAGE({2,4,FALSE}-{1,1,FALSE})
=AVERAGE({1,3,0})
=1.3333333
whereas what I really want is
=AVERAGE({2,4,FALSE}-{1,1,FALSE})
=AVERAGE({1,3,FALSE})
=AVERAGE({1,3})
=2
Is there any way of forcing Excel to exclude FALSE elements from arrays? I would like to apply various built-in functions (i.e. not just AVERAGE) to arrays containing FALSE elements, so it would be wonderful if there was some way of achieving this!
Many thanks,
-Matt
When applying a built-in Excel function (such as AVERAGE) to an array, all FALSE elements are automatically excluded. For example, the formula:
=AVERAGE({2,4,FALSE})
is treated as being equivalent to
=AVERAGE({2,4})
and so both formulae produce the value 3, as expected.
However, the following formula:
=AVERAGE({2,4,FALSE}-{1,1,FALSE})
produces the value 1.33333, which (as far as I'm concerned) is unexpected. I would have expected the above formula to produce the value 2, since the FALSE element in the resulting internal array *should* be ignored, and the formula should be equivalent to
=AVERAGE({2,4}-{1,1})
It would appear that, upon performing the internal subtraction, Excel is replacing the FALSE element with the number zero. So if I'm understanding this correctly, Excel is essentially carrying out the following simplification:
=AVERAGE({2,4,FALSE}-{1,1,FALSE})
=AVERAGE({1,3,0})
=1.3333333
whereas what I really want is
=AVERAGE({2,4,FALSE}-{1,1,FALSE})
=AVERAGE({1,3,FALSE})
=AVERAGE({1,3})
=2
Is there any way of forcing Excel to exclude FALSE elements from arrays? I would like to apply various built-in functions (i.e. not just AVERAGE) to arrays containing FALSE elements, so it would be wonderful if there was some way of achieving this!
Many thanks,
-Matt