Calculate value only if two cell values greater than zero

Hi SG,

Both statements are correct.

Both point out the circumstances under which the shorter formula will succeed and both point out the circumstances under which the shorter formula will fail.

The initial statement, in the question's title, is "Calculate value only if two cell values greater than zero"

The first restatement, in the initial post repeats that: "I would like Numbers to look at two cells, and carry out a calculation only if both those cells are >0"

The second restatement, in the same post repeats: "…if J1>0 and R1>0, then return the result of S1/J1."

…and continues with the parenthetical statement "(if either J1 or R1 are not > 0, then return a blank cell)"

Wayne's 'longer' formula is a direct translation of the specification into an IF statement.

IF(AND(J1>0, R1>0), S1/J1, "")

AND returns true only if J1 is greater than zero AND R1 is also greater than zero.

AND returns false under three conditons: J1 is zero or less,

R1 is zero or less,

both J1 and R1 are zero or less.

The third condition is redundant. If both are zero or less, then the first condition (and the second) are already met.

The shorter formula does not test each value. Instead, it calculates the product of the two values, then tests the result in the IF statement:

IF(J1*R1>0,S1/J1,"")

The multiplication result will be: zero if either (or both) J1 and R1 is zero. (result: "") √

less than zero if J1 is greater than zero and R1 is less than zero (result: "") √

less than zero if R1 is greater than zero and J1 is less than zero (result: "") √

greater than zero if J1 is greater than zero and R1 is greater than zero (result: S1/J1) √

but greater than zero if J1 is less than zero and R1 is less than zero (result: S1/J1) x

Whether the shorter formula is suitable for the job depends on whether negative values are possible for both J1 and R1. If the minimum value for either is zero, the shorter formula will do the job. If J1 and R1 can have negative values, the shorter formula will fail on occasions where both J1 and R1 are negative.

A second issue may also arise, due to the use of the multiplication operator in the shorter formula, and depending on how the values in J1 and R1 are determined.

If those values are entered directly, and an apparently 'blank' cell is really empty, the calculation in the shorter formula should work correctly (except as noted above). If either is the result of a formula that uses a null string ( "" ) to create the appearance of an empty cell, the multiplication operator will likely crash when presented with this text value, producing an error triangle and this message: ' The operator “*” expects a number but found “.” '

Regards,

Barry

Or this ... somewhat shorter:

=IF(J1*R1>0,S1/J1,"")

SG

Hi SG,

=IF(J1*R1>0,S1/J1,"")

Shorter, yes, but not a perfect fit with the specification: "…carry out a calculation only if both those cells are >0"

The formula displayed is entered in U1 and filled down column U to provide several examples.

Column J and R contain the values that must 'both' be '>0"

Column S contains the multiplier.

In the second example (yellow filled cells), S1 is changed to T1. The resulting formula is placed in column W.

Results, should be (and are) the same as those for column U but with the opposite sign.

Note that in row 6, neither J6 nor R6 is greater than zero, but a result has been calculated.

The shorter formula will do the job, provided J1 and R1's minimum value is zero, but not if negative values are possible for these two cells.

Regards,

Barry

Barry wrote:

The shorter formula will do the job, provided J1 and R1's minimum value is zero, but not if negative values are possible for these two cells.

More precisely, the shorter formula will do the job, except when both J1 and R1 are less than 0 at the same time.

It does seem to fit the specification "if either J1 or R1 are not > 0, then return a blank cell."

SG

"It does seem to fit the specification "if either J1 or R1 are not > 0, then return a blank cell.""

Except that it doesn't in row 6, where both J6 AND R6 are "not>0", or in row 9, where both J9 and R9 are "not >0" In both those cases, the formula returns a numerical result, where the specification calls for a blank cell.

What you've quoted above is a parenthetical remark, intended, I expect, to clarify the specification, for which JD had already clearly stated the conditions (twice):

• …carry out a calculation only if both those cells are >0
• So, for example, if J1>0 and R1>0, then return the result of S1/J1.
  (if either J1 or R1 are not > 0, then return a blank cell).

Regards,

Barry

Barry wrote:

"It does seem to fit the specification "if either J1 or R1 are not > 0, then return a blank cell.""

Except that it doesn't in row 6, where both J6 AND R6 are "not>0", or in row 9, where both J9 and R9 are "not >0"

It seems that your row 6 and row 9 are examples of the same case, where the values in J and in R are both negative at the same time.

My "The shorter formula will do the job, except when both J1 and R1 are less than 0 at the same time" seems to be a better statement than your "The shorter formula will do the job, provided J1 and R1's minimum value is zero, but not if negative values are possible for these two cells."🙂

SG

if J1>0 and R1>0, then return the result of S1/J1. (if either J1 or R1 are not > 0, then return a blank cell).

=if(and(J1>0, R1>0), S1/J1, "")

Hi JD,

If E1 and M1 both have a zero or greater value entered, then calculate S1/J1.

If either E1 or M1 are blank (no value entered), then return a blank cell.

See if this works for you

Formula in T2 (and Fill Down) =IF(OR(E2="",M2=""),"",IF(AND(E2≥0,M2≥0),S2÷J2,""))

Regards,

Ian.

Hi JD,

You wrote: "This is my first effort, but it produces an error:"

IF(AND(LEN(E1>0, LEN(M1>0), S1÷J1, "")))

The errors are a syntax error in each of the LEN functions: LEN(value) and misplacement of the closing parenthesis for AND.

LEN(E1) returns the LENgth in number of characters of the content displayed in cell E1. That result may then be compared with the fixed value, 0:

LEN(E1)>0

Which will return true if any character (including the character "0") is displayed in E1.

Applying that correction to both occurrences of LEN in the formula:

IF(AND(LEN(E1)>0, LEN(M1)>0)),S1÷J1,"")

My own preference to achieve the same result would be arrange the formula this way:

IF(OR(LEN(E1)<1,LEN(M1)<1),"",S1÷J1)

For me, this visually separates the action (in bold) from the 'switch' which prevents the action if either test cell has no entry.

Regards,

Barry

(My assumption here is that neither test value—E1 or J1—will drop below zero. Ian's solution above accounts for that possibility.)

B