How does this formula work?

[David Heaton]

I have a list of numbers in rows D728, and another list of numbers in
E7:E28

I then add this formula to rows G14:G24 (the placement is actually
irrelevant)


{=AVERAGE(IF(D$7$28=D14,E$7:E$28,""))} (please note its an array
formula)

can someone explain how this formula works and hence relates to the two
lists


thanks

 

[T. Valko]

Let's use a smaller set of ranges to see how this works.

...........D..........E
1.......10.........10
2.......12.........15
3.......10.........20
4.......17.........20
5.......10.........30

=AVERAGE(IF(D$1$5=D3,E$1:E$5))

The IF function takes 3 arguments, a logical_test, a value_if_ true and a
value_if_ false. In the above formula the logical test is D$1$5=D3, the
value_if_true is E$1:E$5 and the value_if_false has been ommited so it
*defaults* to FALSE.

The logical_test will evaluate to an array of either TRUE or FALSE. Like
this:

D1 = D3 = TRUE
D2 = D3 = FALSE
D3 = D3 = TRUE
D4 = D3 = FALSE
D5 = D3 = TRUE

So, with the logical_test the corresponding value_if_true or the
value_if_false is then passed to the AVERAGE function and we get the average
of those values.. The value_if_true are the values in the range E1:E5 and
the value_if_false is the default FALSE. That would look like this: (T=TRUE,
F=FALSE)

D1 = D3 = T = E1 = 10
D2 = D3 = F = F = F
D3 = D3 = T = E3 = 20
D4 = D3 = F = F = F
D5 = D3 = T = E5 = 30

So, at this point the average function looks like this:

=AVERAGE({10,FALSE,20,FALSE,30}) = 20

AVERAGE ignores logical values (TRUE,FALSE) and text values that are
*elements of an array* so we get the average of 10, 20, and 30.

The formula you posted uses an empty TEXT string as the value_if_false
argument:

=AVERAGE(IF(D$7$28=D14,E$7:E$28,""))

So, this is what the average function would look like (based on the ranges
I've used in this example):

=AVERAGE({10,"",20,"",30}) = 20

 

[David Heaton]

thanks very much the explanation was great.

I really didnt fully understand how array formulas worked and this has
helped a lot.

Thanks again

David

 

[TheHeatons]

Let's use a smaller set of ranges to see how this works.

..........D..........E
1.......10.........10
2.......12.........15
3.......10.........20
4.......17.........20
5.......10.........30

=AVERAGE(IF(D$1$5=D3,E$1:E$5))

The IF function takes 3 arguments, a logical_test, a value_if_ true and a
value_if_ false. In the above formula the logical test is D$1$5=D3, the
value_if_true is E$1:E$5 and the value_if_false has been ommited so it
*defaults* to FALSE.

The logical_test will evaluate to an array of either TRUE or FALSE. Like
this:

D1 = D3 = TRUE
D2 = D3 = FALSE
D3 = D3 = TRUE
D4 = D3 = FALSE
D5 = D3 = TRUE

So, with the logical_test the corresponding value_if_true or the
value_if_false is then passed to the AVERAGE function and we get the average
of those values.. The value_if_true are the values in the range E1:E5 and
the value_if_false is the default FALSE. That would look like this: (T=TRUE,
F=FALSE)

D1 = D3 = T = E1 = 10
D2 = D3 = F = F = F
D3 = D3 = T = E3 = 20
D4 = D3 = F = F = F
D5 = D3 = T = E5 = 30

So, at this point the average function looks like this:

=AVERAGE({10,FALSE,20,FALSE,30}) = 20

AVERAGE ignores logical values (TRUE,FALSE) and text values that are
*elements of an array* so we get the average of 10, 20, and 30.

The formula you posted uses an empty TEXT string as the value_if_false
argument:

=AVERAGE(IF(D$7$28=D14,E$7:E$28,""))

So, this is what the average function would look like (based on the ranges
I've used in this example):

=AVERAGE({10,"",20,"",30}) = 20

--
Biff
Microsoft Excel MVP










- Show quoted text -

thanks very much the explanation was great.

I really didnt fully understand how array formulas worked and this
has
helped a lot.

Thanks again

David

 

[T. Valko]

thanks very much the explanation was great.

I really didnt fully understand how array formulas worked and this
has
helped a lot.

Thanks again

David

You're welcome. Thanks for the feedback!

 

[ilia]

A good way to figure these out is to use the Tools -> Formula Auditing
-> Evaluate. This will, usually, list all the intermediate array
results.

 

[TheHeatons]

A good way to figure these out is to use the Tools -> Formula Auditing
-> Evaluate. This will, usually, list all the intermediate array
results.








- Show quoted text

Fantastic...thats something else I wasnt aware of....thanks a lot