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
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
