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

[StargateFanNotAtHome]

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! D

 

[Pete_UK]

Your de3scription is a bit confusing because you say you want to put ?
when you don't know the amount to pay (column C), and then show it in
column B in your example. You also refer to column 4, but I think you
mean row 4.

Anyway, going from your example, put this in the Balance column (cell
D4):

=IF(B4="?",TEXT(C4,"$0")&" to pay",B4-C4)

Then you can copy this down.

Hope this helps.

Pete

 

[Niek Otten]

=IF(B4="?",TEXT(C4,"$0")&" to pay",B4-C4)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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! D

 

[wknehans]

B$="?",StargateFan,

You could Use the Concatenate function to add some text to whatever value
you do know. Try the following formula in J4:

=IF(B4="?",Concatenate(C4," to pay"),B4-C4)

Also, I'm not sure why you are using the SUM function. If you know the
balance and know the amount to be paid, just subtract them:

=B4-C4

Good luck!

BD

 

[StargateFanNotAtHome]

Your de3scription is a bit confusing because you say you want to put ?
when you don't know the amount to pay (column C), and then show it in
column B in your example. You also refer to column 4, but I think you
mean row 4.

Anyway, going from your example, put this in the Balance column (cell
D4):

=IF(B4="?",TEXT(C4,"$0")&" to pay",B4-C4)

Then you can copy this down.

Hope this helps.

Yes! <g> That seems to be working perfectly. If I have a value in
column B, column J shows a balance. If I switch any value in column B
to a "?" symbol, as sometimes an outstanding balance still isn't
known, then the J column just "repeats" what is in column C (the
amount to pay) with the additional text.

See, even when exact balance owing isn't known, it's best to keep up a
regular payment of some sort. We don't wait for all balances to come
in but go ahead and make an estimate payment since these are recurring
expenses.

Thanks! D

 

[StargateFanNotAtHome]

=IF(B4="?",TEXT(C4,"$0")&" to pay",B4-C4)

Thank you!

Likewise. D

 

[StargateFanNotAtHome]

B$="?",StargateFan,

You could Use the Concatenate function to add some text to whatever value
you do know. Try the following formula in J4:

=IF(B4="?",Concatenate(C4," to pay"),B4-C4)

Also, I'm not sure why you are using the SUM function. If you know the
balance and know the amount to be paid, just subtract them:

Yes, that what the formula does in its now earlier incarnation <g>.
But sometimes, balances haven't come in so we don't know the exact
outstanding amount to pay. We still must make a payment to these
regular recurring expenses so nice that the spreadsheet now can work
with this situation. In order to not get a #VALUE, I'd put in a zero
so that the payment would still show up. The recommended way is
better. Column J might not show an outstanding balance, but it does
now reflect the payment to be made.

=B4-C4

Good luck!

Thank you! D

 

[Pete_UK]

You're welcome - thanks for feeding back, and glad to be of help.

Pete