Results 1 to 5 of 5
Thread: Array formula explanation?

20120519, 23:42 #1
 Join Date
 Aug 2001
 Location
 Bay Area, California, USA
 Posts
 972
 Thanks
 19
 Thanked 4 Times in 4 Posts
Array formula explanation?
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))

20120520, 05:05 #2
 Join Date
 Apr 2002
 Location
 Redditch, Worcestershire, England
 Posts
 233
 Thanks
 0
 Thanked 22 Times in 21 Posts
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 5day period.

20120520, 13:32 #3
 Join Date
 Aug 2001
 Location
 Bay Area, California, USA
 Posts
 972
 Thanks
 19
 Thanked 4 Times in 4 Posts
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?

20120520, 14:37 #4
 Join Date
 Apr 2002
 Location
 Redditch, Worcestershire, England
 Posts
 233
 Thanks
 0
 Thanked 22 Times in 21 Posts
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.

20120520, 14:39 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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