Array Formula: Does anyone see anything wrong with the formula?

I'm working through an Excel text book (Benchmark Series). There is a
question (Concept Checks- Chapter E3 #16) that asks, "What is wrong with the
following array formula:"

{=SUM(IF(A5:I5=B5:B10,1,0))}

I set all the cells to a value of 5 and the formula returns a 54, which I
think is correct. Does anyone see anything wrong with the formula?

Agreed, if you compare each element of a 6 element array by each element of
a 9 element array, you get 54 if they all are equal. But I would have
thought that it is trying to compare each element of an array against it's
corresponding element in another array. For this, the arrays need to be the
same size. And then they should be in the same plane to be compared, one is
currently a row, one is a column, so you should transpose the row elements.

This then becomes

=SUM(IF(TRANSPOSE(A5:I5)=B5:B13,1,0))

which will give 9 with your data.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Matthew S" <[email protected]> wrote in message
news:[email protected]...
> I'm working through an Excel text book (Benchmark Series). There is a
> question (Concept Checks- Chapter E3 #16) that asks, "What is wrong with
the
> following array formula:"
>
> {=SUM(IF(A5:I5=B5:B10,1,0))}
>
> I set all the cells to a value of 5 and the formula returns a 54, which I
> think is correct. Does anyone see anything wrong with the formula?

Depends what it's supposed to be doing, I'd say The SUM part of the formula
is not required, however I can't see how you are getting the answer 54 when
the IF function says return either 1 or 0

"Matthew S" wrote:

> I'm working through an Excel text book (Benchmark Series). There is a
> question (Concept Checks- Chapter E3 #16) that asks, "What is wrong with the
> following array formula:"
>
> {=SUM(IF(A5:I5=B5:B10,1,0))}
>
> I set all the cells to a value of 5 and the formula returns a 54, which I
> think is correct. Does anyone see anything wrong with the formula?

Sheila,

It compares every element of A5:I5 in turn against every element of B5:B10,
so it compares
A5 to B5
A5 to B6
A5 to B7
A5 to B8
A5 to B9
A5 to B10
B5 to B5
B5 to B6
B5 to B7
B5 to B8
B5 to B9
B5 to B10
C5 to B5
C5 to B6
etc.

For every match it adds 1 to a result array, mis-matches adds a 0 to that
array. SUM just sums all the elements of the result array. As Matthew set
all cells to 5, all would match, all would add 1 to the result array, there
are 9 elements in A5:I5, 6 in B5:B10, 6x9 is 54, so the sum will return 54.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sheila D" <[email protected]> wrote in message
news:[email protected]...
> Depends what it's supposed to be doing, I'd say The SUM part of the
formula
> is not required, however I can't see how you are getting the answer 54
when
> the IF function says return either 1 or 0
>
> "Matthew S" wrote:
>
> > I'm working through an Excel text book (Benchmark Series). There is a
> > question (Concept Checks- Chapter E3 #16) that asks, "What is wrong with
the
> > following array formula:"
> >
> > {=SUM(IF(A5:I5=B5:B10,1,0))}
> >
> > I set all the cells to a value of 5 and the formula returns a 54, which
I
> > think is correct. Does anyone see anything wrong with the formula?

I took me sometime, but after a little practice, I'm pretty sure I
understand. Thanks for sharing your knowledge!!!

The first formula {=SUM(IF(A5:I5)=B5:B10,1,0))} does in fact compare each
cell in the first array to each cell in the second array and when they are
equal adds 1 to the answer or (9*6) =54. The textbook says there is
something wrong with that formula. But, it seems to me it does what it’s
supposed to do.

I’m sure your analysis is beyond the scope of my course. I had no
experience with the TRANSPOSE function so I learned something new - THANKS.

I learned that the second formula {SUM(IF(TRANSPOSE(A5:I5)=B5:B13,1,0))} in
effect puts the arrays in same plane. If the arrays are in the same plane,
e.g., {SUM(IF( (A5:A13)=B5:B13,1,0))} the formula compares corresponding
elements and the Result= 9 (the arrays have to be the same size)

If the arrays are in different planes it compares each element in the first
array to each element in the second array (the arrays don’t have to be the
same size).

THANKS AGAIN FOR SHARING YOUR KNOWLEDGE!!!



"Bob Phillips" wrote:

> Agreed, if you compare each element of a 6 element array by each element of
> a 9 element array, you get 54 if they all are equal. But I would have
> thought that it is trying to compare each element of an array against it's
> corresponding element in another array. For this, the arrays need to be the
> same size. And then they should be in the same plane to be compared, one is
> currently a row, one is a column, so you should transpose the row elements.
>
> This then becomes
>
> =SUM(IF(TRANSPOSE(A5:I5)=B5:B13,1,0))
>
> which will give 9 with your data.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Matthew S" <[email protected]> wrote in message
> news:[email protected]...
> > I'm working through an Excel text book (Benchmark Series). There is a
> > question (Concept Checks- Chapter E3 #16) that asks, "What is wrong with
> the
> > following array formula:"
> >
> > {=SUM(IF(A5:I5=B5:B10,1,0))}
> >
> > I set all the cells to a value of 5 and the formula returns a 54, which I
> > think is correct. Does anyone see anything wrong with the formula?
>
>
>