Excluding FALSE elements from arrays...

[mrzippy]

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

 

[shg]

Amen. Or something else. Or anything else.

 

[mrzippy]

You could always go to something like


(SUM({4,2,FALSE})-SUM({1,1,False}))/COUNT({4,2,FALSE})

Thanks for the suggestion! That would definitely work for my example data. Another possibility would be to split the calculation into two parts, giving:


=AVERAGE({2,4,FALSE})-AVERAGE({1,1,FALSE})


However, this relies on the property of averages (namely, AVERAGE(x-y) = AVERAGE(x)-AVERAGE), and in general not all functions could be split up in this way. For instance, a function such as VAR wouldn't work in this way.

Maybe


=AVERAGE(IF(ISNUMBER({2,4,FALSE}) * ISNUMBER({2,1,FALSE}), {2,4,FALSE} - {2,1,FALSE}))

That certainly seems to solve my original problem in its entirety -- thanks!

My question is what do you want to do if the Falses occur in different places in the array




What would you want returned for AVERAGE({4, 2, FALSE} - {1, FALSE, 1})

That's an excellent question! In the example you've provided, I would like the FALSE elements to be omitted, thereby producing a result of 2 (as before). Essentially, I would like some way of forcing Excel to ignore all of the FALSE (or non-number, if that makes it easier) elements. So, ideally, the following would all produce the same result:


=AVERAGE({4,2}-{1,1})
=AVERAGE({4, 2, FALSE} - {1, 1, FALSE})
=AVERAGE({4, 2, FALSE} - {1, FALSE, 1})


Maybe I'm looking at this problem the wrong way. Would it help if FALSE was replaced with something else (such as "")? Does Excel support a NULL value or an NA value that could be used in place of FALSE? In the R programming language, for example, NA values can be identified in (and removed from) arrays using the is.na() function... perhaps there's a similar function in Excel?

 

[mikerickson]

What would you want returned for AVERAGE({4, 2, FALSE} - {1, FALSE, 1})

In the example you've provided, I would like the FALSE elements to be omitted, thereby producing a result of 2

How does that result in 2? I would read "False elements omitted" to omit the second and third elements of each array, leaving only the first element {4}-{1} which would result in 3.

Perhaps a different situation will explain what you mean.

=AVERAGE({4, 2, False, 4}-{1, False, 1, False})

 

[mrzippy]

How does that result in 2? I would read "False elements omitted" to omit the second and third elements of each array, leaving only the first element {4}-{1} which would result in 3.

I'm wanting the FALSE elements removed prior to the subtraction. In the first array, the third element is FALSE, so the first array should reduce to {4,2}. In the second array, the second element is FALSE, so the second array should reduce to {1,1}. Performing the subtraction then gives:

{4,2}-{1,1} = {3,1}

and then taking the average gives:

=AVERAGE({3,1})

which is equal to 2.

Perhaps a different situation will explain what you mean.

=AVERAGE({4, 2, False, 4}-{1, False, 1, False})

Removing FALSE elements from the first array gives {4,2,4} (i.e. an array of length 3), whilst removing FALSE elements from the second array gives {1,1} (i.e. an array of length 2). So in this case, upon removing the FALSE elements (again, prior to the subtraction), we end up with two arrays that are of different lengths -- so I would expect an error.

I hope this makes it a little bit clearer. I'm basically looking for some function that, when applied to an array, removes all FALSE elements (thereby changing the length of the array). Does such a function exist?

 

[mikerickson]

Actualy, that makes things harder.

Ususualy when one is doing arithmetic on arrays, there is some logical (non Excel) connection between the same elements of the different arrays.

i.e. in
{4, 2, False, 4}
{1, False, 1, False}
the (4,1) (2,False) (False,1) and (4,False) are pairs of some kind

Just ignoring the False and shifting the array indices makes a connection between (2,1) that isn't supported by the original data.

 

[mrzippy]

Ususualy when one is doing arithmetic on arrays, there is some logical (non Excel) connection between the same elements of the different arrays.

i.e. in
{4, 2, False, 4}
{1, False, 1, False}
the (4,1) (2,False) (False,1) and (4,False) are pairs of some kind

Just ignoring the False and shifting the array indices makes a connection between (2,1) that isn't supported by the original data.

I completely agree with you. That's why I want to find a way of removing the FALSE elements before performing the arithmetic operation.

I'm a complete novice in terms of Excel (I've only started using it this week, because I'm doing some work for somebody who insists that I provide them with a working solution in the form of an Excel spreadsheet). My go-to environment for doing this sort of thing would usually be the R programming language, where "empty" elements are typically stored as 'NA'. R has a function that can be used to identify (and remove) NA values from arrays. For example, if the array 'y' is defined as {1,NA,1}, then (in R-speak) y[!is.na] is equal to {1,1}. The R code "y[!is.na]" is essentially saying:

1. y[!is.na] -- give me y...
2. y[!is.na] -- ... but remove all elements...
3. y[!is.na(y)] -- ... that are NA...
4. y[!is.na(y)] -- ... in y.

So if we define two arrays in R and perform a subtraction, then:

x = c(4,2,NA);
y = c(1,NA,1);
x-y

will produce {3,NA,NA}. However, if we exclude the NA values (using the is.na() function) from each array before performing the subtraction, then

x = c(4,2,NA);
y = c(1,NA,1);
x[!is.na(x)]-y[!is.na(y)]

will produce {3,1}. I'm basically wanting to find a way of achieving the Excel-equivalent of "x[!is.na(x)]" (if such an equivalent exists).

Does anybody have any suggestions?

---

In the interests of completeness, the following

x <- c(4,2,NA,4);
y <- c(1,NA,1,NA);
x-y

would produce {3,NA,NA,NA}, since both arrays are the same length, whilst

x <- c(4,2,NA,4);
y <- c(1,NA,1,NA);
x[!is.na(x)]-y[!is.na(y)]

would produce a warning stating that the two arrays are of different lengths.

 

[XOR LX]

Hi.

I guess you could use an array formula**:

=AVERAGE(INDEX(Arry1,N(IF(1,MODE.MULT(IF(Arry1,{1;1}*COLUMN(Arry1))))))-INDEX(Arry2,N(IF(1,MODE.MULT(IF(Arry2,{1;1}*COLUMN(Arry1)))))))

which presumes that both Arry1 and Arry2 are row-vectors (as in all of your examples).

The values in the post-reduction arrays maintain the order of the pre-reduction arrays. #N/A will result if the dimensions of Arry1 and Arry2, post-reduction, are not equal.

Edit: I should have clarified that the above set-up is only valid if Arry1 and Arry2 refer to actual worksheet ranges. If they are array constants, you will need:

=AVERAGE(INDEX(Arry1,N(IF(1,MODE.MULT(IF(Arry1,{1;1}*COLUMN(INDEX(1:1,1):INDEX(1:1,COUNTA(Arry1))))))))-INDEX(Arry2,N(IF(1,MODE.MULT(IF(Arry2,{1;1}*COLUMN(INDEX(1:1,1):INDEX(1:1,COUNTA(Arry2)))))))))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[XOR LX]

=AVERAGE(INDEX(Arry1,N(IF(1,MODE.MULT(IF(Arry1,{1;1}*COLUMN(Arry1))))))-INDEX(Arry2,N(IF(1,MODE.MULT(IF(Arry2,{1;1}*COLUMN(Arry1)))))))

Sorry. Very careless. Should have been:

=AVERAGE(INDEX(Arry1,N(IF(1,MODE.MULT(IF(Arry1,{1;1}*COLUMN(Arry1)-MIN(COLUMN(Arry1))+1)))))-INDEX(Arry2,N(IF(1,MODE.MULT(IF(Arry2,{1;1}*COLUMN(Arry2)-MIN(COLUMN(Arry2))+1))))))

Regards

 

[mrzippy]

=AVERAGE(INDEX(Arry1,N(IF(1,MODE.MULT(IF(Arry1,{1;1}*COLUMN(Arry1)-MIN(COLUMN(Arry1))+1)))))-INDEX(Arry2,N(IF(1,MODE.MULT(IF(Arry2,{1;1}*COLUMN(Arry2)-MIN(COLUMN(Arry2))+1))))))

Genius! Thank you very much for your help, XOR LX. That does exactly what I want.

The problem can now be considered solved.

 

[XOR LX]

You're welcome!

Cheers!