On Mon, Sep 22, 2008 at 11:30 AM, Brian Barker
<[EMAIL PROTECTED]> wrote:
> At 10:16 22/09/2008 -0700, Mark Knecht wrote:
>>
>> I have some data that is in two columns and looks something like this:
>>
>> 10 .39
>> -10 .54
>> -10 .67
>> 10 .45
>>
>> I need to get the sum of column 1 * column 2 but only when the value in
>> column 1 is either negative or positive.
>
> But *all* your sample values are "either negative or positive"! I imagine
> you mean that you need one of two possible results: either for negative or
> positive values.
>
>> Is it possible to do it in a single statement?
>
> Yes: you can do this with an array formula. Type something like:
> =SUM((A1:A4>0)*A1:A4*B1:B4)
> into your target cell. Instead of pressing Enter to complete the formula,
> press Ctrl+Shift+Enter. (If you use the Function Wizard, tick the Array box
> in the bottom left corner.) This formula will now display in the Input Line
> enclosed in braces:
> {=SUM((A1:A4>0)*A1:A4*B1:B4)}
> - but note that you cannot achieve the same result by typing the braces
> yourself.
>
> The formula works because the condition in the first part returns a value
> TRUE or FALSE, but these logical values are stored as one and zero and can
> be misinterpreted as these values in a numerical expression, as here.
> Change ">" to "<" for the other formula, of course.
>
> I trust this helps.
>
> Brian Barker
Thanks Brian. When entered this as an array formula this seems to work
the way I needed:
{=SUM((A1:A4>0)*A1:A4*B1:B4)}
I don't really understand the concept of array formulas so I suppose
that's part of why I didn't try it. I had actually entered exactly
this formula earlier as a non-array formula and dismissed it as not
working. The formula makes sense but why is it that array formulas
make it work?
Thanks,
Mark
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
