=(A1:A4=(B1-C1)) Question

[J]

I'm trying to select the value of a cell that matches the value of two cells
summed. something like this

A B C
1 6 1
3
5
7


=5*(A1:A4=(B1-C1))

=5*(A1:A4=(5))

=25

i know that this isnt the best example. here's the actual formula:
=SUM(IC16*(1-ID$5),IF(IB16-ID$4<0,ID$5*(IF(IB$3:IF$3=(IB16-ID$4),IB$3:IF$3,0)),0))

in using this formula, i get an error from the first part "A1:A4". any
ideas? thanks

 

[Jerry W. Lewis]

Did you array enter (Ctrl-Shift-Enter) your formula?

When array entered, =5*(A1:A4=(B1-C1)) returns an array of 4 numbers,
the third being 5 and the rest being 0. Since you seem to want an
answer of 25, you need to give a better description of what you
intended, rather than what your formula actually does.

A1:A4=(B1-C1) returns an array of boolean values, the third being TRUE
and the rest being FALSE. When you multiply that array by 5, TRUE is
coerced to 1 and FALSE is coerced to 0.

Jerry