Do formula as is unless source cell has "?" in it?

Giganews Newsgroups
Subject: Do formula as is unless source cell has "?" in it?
Posted by:  StargateFanNotAtHome (IDon'tAcceptSp…@NoJunkMail.com)
Date: Wed, 30 Jul 2008

I've searched in the forum but haven't had much luck.  I guess because
I don't know the name of the function that would be used.

I have this formula in J4:  =SUM(B4-C4)
B4 shows the amount owing
C4 shows what will be paid

I've been finding over last few weeks that it would actually be a good
idea if I could put "?" in column 4 rather than a "0" when I don't
know what the amount to pay is.  But when I do that, J4 understandably
has a "#VALUE" instead of a balance.

So how can =SUM(B4-C4) be modified to work if there is a number in it
but that will also compensate for when a "?" symbol is in column 4 so
that column J will show something like "$x to pay" instead of a
balance.

Actual examples:

                  B4          C4
                Owing    To Pay  Balance
                -----------    -----------  --------------
Case1      $150      $100    $50
Case 2        ?          $10      $10 to pay

Thank you!  :oD

Replies