At 16:08 29/05/2007 -0400, Stephan Michelson wrote:
I want to condition a calculation on a value in a column (that is,
the same cell in each row). Example: balance of only cleared checks
and deposits in a bank account. I place an x in the column "clear,"
and want to be able to tell Calc that a certain row contains
conditions. If, in that row, there is an x under "clear," then only
those rows with such an x will be counted. This is done through the
"criterion" option under "sum" in Excel.
-- SM
There are various ways of doing this.
The simplest way is probably the SUMIF function. It has three
parameters: the first is the range of values to be tested, the second
is the test itself, and the third is the range to be (conditionally)
summed. Note that the test, if it is included directly in the
function reference, needs to be enclosed in double quotation
marks. Suppose your values are in column A and the "x" marks are in
column B. The expression you need would be something like:
=SUMIF(B1:B10;"=x";A1:A10)
There is also a way of including conditions in the SUM function. You
need something like:
{=SUM((B1:B5="x")*A1:A5)}
This relies on the logical values returned by the condition being
interpreted as 1 (for TRUE) and 0 (for FALSE) when misinterpreted as
a number. You don't type in the braces, by the way: instead, you
close the formula by pressing Ctrl+Shift+Enter instead of plain
Enter. See the help text on the SUM function for details.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
