G
Guest
Hi,
I have encountered the following problem with array formulas, which is
described below (after the introduction that sets up an example).
Let's imagine a dataset where columns B through E represents years 2000
through 2003 (which are entered into row 1) and rows 2, 3, 4 and 5 represents
North, South, East, and West (which are entered into column A) so that the
data that populates the table covers B2:E5. Let's further imagine that cell
D5 is left blank. All values are positive integers.
Using Boolean logic and an array function, I can pickup the cross section of
East and 2002 by submitting the array formula
{=SUM(B2:E5*(2002=B1:E1)*("east"=A2:A5))}. Furthermore, I can count the
number of observations in East and West by submitting
{=SUM(1*ISNUMBER(B2:E5)*(("west"=A2:A5)+("east"=A2:A5)))}, which returns 7
(keeping in mind that D5 is blank). Now, if I want to find the sum of east
and west, I would substitute "1" with B2:E5, so that my array formula looks
like {=SUM(B2:E5*ISNUMBER(B2:E5)*(("west"=A2:A5)+("east"=A2:A5)))} (which
with my unique dataset returns 64). I am aware that the ISNUMBER term is
redundant in calculation example.
Now, lets say that rather than being blank, cell D5 contains a formula that
returns "", such as ="". If I want to count the number of numerical
observations in East and West, I can still enter
{=SUM(1*ISNUMBER(B2:E5)*(("west"=A2:A5)+("east"=A2:A5)))}, which returns 7.
However, and this is the problem, if I want to find the sum of East and West
and enter {=SUM(B2:E5*ISNUMBER(B2:E5)*(("west"=A2:A5)+("east"=A2:A5)))},
Excel returns an error (#VALUE!). This problem has been bugging me for a
while and I would really appreciate a solution. The solution should not be to
change the contents of cell D5 or to simply take SUM(B4:E5). Usually I
wouldn't have entered "west" and "east" into the formula, but these would
actually reference some other input. It is important that the sum calculation
would dynamically update if, for instance, "east" was changed to "south". I
hope this makes sense and I appreciate any suggestions.
Thanks,
Henrik
I have encountered the following problem with array formulas, which is
described below (after the introduction that sets up an example).
Let's imagine a dataset where columns B through E represents years 2000
through 2003 (which are entered into row 1) and rows 2, 3, 4 and 5 represents
North, South, East, and West (which are entered into column A) so that the
data that populates the table covers B2:E5. Let's further imagine that cell
D5 is left blank. All values are positive integers.
Using Boolean logic and an array function, I can pickup the cross section of
East and 2002 by submitting the array formula
{=SUM(B2:E5*(2002=B1:E1)*("east"=A2:A5))}. Furthermore, I can count the
number of observations in East and West by submitting
{=SUM(1*ISNUMBER(B2:E5)*(("west"=A2:A5)+("east"=A2:A5)))}, which returns 7
(keeping in mind that D5 is blank). Now, if I want to find the sum of east
and west, I would substitute "1" with B2:E5, so that my array formula looks
like {=SUM(B2:E5*ISNUMBER(B2:E5)*(("west"=A2:A5)+("east"=A2:A5)))} (which
with my unique dataset returns 64). I am aware that the ISNUMBER term is
redundant in calculation example.
Now, lets say that rather than being blank, cell D5 contains a formula that
returns "", such as ="". If I want to count the number of numerical
observations in East and West, I can still enter
{=SUM(1*ISNUMBER(B2:E5)*(("west"=A2:A5)+("east"=A2:A5)))}, which returns 7.
However, and this is the problem, if I want to find the sum of East and West
and enter {=SUM(B2:E5*ISNUMBER(B2:E5)*(("west"=A2:A5)+("east"=A2:A5)))},
Excel returns an error (#VALUE!). This problem has been bugging me for a
while and I would really appreciate a solution. The solution should not be to
change the contents of cell D5 or to simply take SUM(B4:E5). Usually I
wouldn't have entered "west" and "east" into the formula, but these would
actually reference some other input. It is important that the sum calculation
would dynamically update if, for instance, "east" was changed to "south". I
hope this makes sense and I appreciate any suggestions.
Thanks,
Henrik