Array formula explanation?

Topic

New Reply

Below is a formula that someone gave me that I use to compute sleep time over the past 5 days (I have similar formulas for 10 and 30 days).

Column A is the date and column J is the total time spent sleeping in hours:minutes.

I really don’t understand how these array formulas work. I’ve looked at the Excel help file and browsed some websites looking for a detailed description of a similar formula but haven’t yet found one.

Can someone give me a detailed explanation of what is going on as this formula iterates from row 4 forward so I can try and get my head around what is going on and how it is going on?

=AVERAGE(IF(ISNUMBER(J4:J370)*(A4:A370>((TODAY()-1)-5))*(A4:A370<=(TODAY()-1)),J4:J370))

Reply | Quote

Replies

Below is a formula that someone gave me that I use to compute sleep time over the past 5 days (I have similar formulas for 10 and 30 days).

Column A is the date and column J is the total time spent sleeping in hours:minutes.

I really don’t understand how these array formulas work. I’ve looked at the Excel help file and browsed some websites looking for a detailed description of a similar formula but haven’t yet found one.

Can someone give me a detailed explanation of what is going on as this formula iterates from row 4 forward so I can try and get my head around what is going on and how it is going on?

=AVERAGE(IF(ISNUMBER(J4:J370)*(A4:A370>((TODAY()-1)-5))*(A4:A370<=(TODAY()-1)),J4:J370))

Without being able to see the original data, it looks like it's computing the AVERAGE of a set of numbers selected from the range J4:J370. However, the numbers only contribute to the AVERAGE if the test within the IF statement evaluates to TRUE. This will only happen if each of the individual logical tests has a value of TRUE. This happens when:
the cell in the range J4:J370 is a NUMBER
AND
the value in the corresponding row in the range A4:A370 is greater than yesterday – 5
AND
the value in the corresponding row in the range A4:A370 is less than or equal to yesterday (i.e. TODAY() – 1)

Presumably column A holds a range of dates which is what allows you to test for a 5-day period.

Reply | Quote

Yes, as I mentioned originally, column A is the date. The original data is either a total time or is blank. That is the purpose of testing for if there is a number in the cell. Otherwise counting blank cells messes up the average.

I am looking for a more detailed explanation for what this formula is doing than your high level overview (which I understood).

What I don’t grasp is what is happening in an operation like this:

(J4:J370)*(A4:A370)

Why am I multiply cell J4 by A4 and so on, down the line?

Reply | Quote

Yes, as I mentioned originally, column A is the date. The original data is either a total time or is blank. That is the purpose of testing for if there is a number in the cell. Otherwise counting blank cells messes up the average.

I am looking for a more detailed explanation for what this formula is doing than your high level overview (which I understood).

What I don’t grasp is what is happening in an operation like this:

(J4:J370)*(A4:A370)

Why am I multiply cell J4 by A4 and so on, down the line?

You’re not!! As I probably didn’t explain very well in my first post, you’re multiplying the result of 3 logical tests like so:

IF((test1)*(test2)*(test3),range of numbers)

Each of test1, test2, test3 can have a result of TRUE or FALSE. The rules of Boolean logic say that only when all 3 values are TRUE will the resulting expression be TRUE, and only in this situation does the IF statement return a value from the corresponding cell in “range of numbers”.

So, for example if test1 is the expression ISNUMBER(J4:J370), this expression only evaluates to TRUE for a cell in that range that contains a number. If, for example, cell J17 contained the word “fred” then the ISNUMBER test returns FALSE, which is an internal value of 0, so you would get no contribution from cell J17.

Reply | Quote

You are not multiplying A4 by J4 you are doing “boolean math” multiplying TRUES and FALSES. Take Row4 for example. You have three vslues:
ISNUMBER(J4)
(A4>((TODAY()-1)-5)
(A4<=(TODAY()-1))

Each one will give you a TRUE or FALSE. When used in arithmatic, TRUEs acts as a 1 and FALSEs act as a 0. Thus if ALL the individuals are true, you will get the value of 1 (TRUE). If any of them are false (0) you will get 0 (False) as a result. Thus multiplication acts as an AND. If all the values are TRUE then you use the value from J4. Thus you create an array of the values in Col J which is true for all the items (and has the value of false for the others). The formula then averages those value.

It would be akin to creating an intermediate column testing the 3 conditions and if True grabbing the value from J and if false keeping the value FALSE, and then averaging that intermediate column.

Steve

Reply | Quote