sum if

[Stefan]

Hi,

I looking for an easy formula that will do the following procedure:

In cell A3

If cell A1 contains a value (0 - infinity) show value of A1, if not show
value in cell A2

Regards Stefan

 

[Pete_UK]

Try this:

=IF(ISNUMBER(A1),A1,A2)

Hope this helps.

Pete

 

[Mike H]

Hi,

try this in a3

=IF(AND(ISNUMBER(A1),A1>=0),A1,A2)

Mike

 

[JoeU2004]

I looking for an easy formula that will do the following procedure:
[....]
If cell A1 contains a value (0 - infinity) show value of A1,
if not show value in cell A2

Often, the following is sufficient:

=IF(A1<>"", A1,A2)

But if A1 might contain other text and you want A2 in that case, then you
need:

=IF(ISNUMBER(A1),A1,A2)

I also note that you say "0 to infinity". If A1 might contain a negative
number and you want A2 in that case as well, then you need one of the
following:

=IF(AND(A1<>"",A1>=0),A1,A2)

=IF(AND(ISNUMBER(A1),A1>=0),A1,A2)

Finally, if A1 might contain an Excel error (e.g. #NUM, #DIV/0 or #NA) and
you want A2 in that case as well, then you need one of the following:

=IF(ISNUMBER(A1),A1,A2)

=IF(ISNUMBER(A1),IF(A1>=0,A1,A2),A2)

But generally, is preferrable to avoid Excel errors in a cell.


----- original message -----