How can Balance cell attributes be dependent on _either_ debit or credit column?

[StargateFanFromWork]

Good Morning! I've run into trouble again with an IF situation <g>. I have
two columns, one shows amount owing to an account and the other shows amount
paid to that account, in other words, a debit and a credit column. In
making the balance column, I don't know how to show the balance whether or
not both cells are filled in either column. I have conditional formatting
to blank out, as it were, the balance column until there is a value in the
first column because that's all I know how to do. I'd like to have the
balance column "appear" if there is an amount in either. It might be easier
to see an example:

Debit Credit Balance
$11.14 $11.14 $0.00
$1,837.19 $62.19 $1,775.00

I currently only know how have the balance cells appear with colours other
than the default font and background, etc., of the spreadsheet when either
cell has a value. i.e., the formula for the $1775.00 above in the
conditional formatting is this:
=NOT(ISBLANK($B3))
with custom formatting so that the cell that shows the $1775.00 stands out
with different attributes to the rest of the spreadsheet.

How can I have the $1775.00 show up when either cell is filled, i.e., when
either the cells has a value in it where the $1837.19 is now, or when the
one that has $62.19 is filled. The balance, obviously will show the result
as either positive or negative unless the balance is zero, so that issue is
taken care of in the formula itself. It's just to have the display come up
when either cell has a value in it, and that's where I'm stuck.

I hope I've explained this well enough. TIA! D

 

[StargateFanFromWork]

Good Morning! I've run into trouble again with an IF situation <g>. I have
two columns, one shows amount owing to an account and the other shows amount
paid to that account, in other words, a debit and a credit column. In
making the balance column, I don't know how to show the balance whether or
not both cells are filled in either column. I have conditional formatting
to blank out, as it were, the balance column until there is a value in the
first column because that's all I know how to do. I'd like to have the
balance column "appear" if there is an amount in either. It might be easier
to see an example:

Debit Credit Balance
$11.14 $11.14 $0.00
$1,837.19 $62.19 $1,775.00

I currently only know how have the balance cells appear with colours other
than the default font and background, etc., of the spreadsheet when either
cell has a value. i.e., the formula for the $1775.00 above in the
conditional formatting is this:
=NOT(ISBLANK($B3))
with custom formatting so that the cell that shows the $1775.00 stands out
with different attributes to the rest of the spreadsheet.

How can I have the $1775.00 show up when either cell is filled, i.e., when
either the cells has a value in it where the $1837.19 is now, or when the
one that has $62.19 is filled. The balance, obviously will show the result
as either positive or negative unless the balance is zero, so that issue is
taken care of in the formula itself. It's just to have the display come up
when either cell has a value in it, and that's where I'm stuck.

I hope I've explained this well enough. TIA! D

The conditional formatting is what needs changing. I'm kept coming back to
the above post and I'm not certain I've been clear enough on that.

Currently, I have these 2 conditions in the "balance" column:
Condition 1: Formula is =(ISBLANK($B3)) -- cell formatting blanks out cell
to conform to empty background of spreadsheet
Condition 2: Formula is =NOT(ISBLANK($B3)) -- cell formatting pops up and
shows cell contents with light yellow background and blue text, etc.

But currently, this only works when B3 has a value entered (debit column).
If there is nothing in B3 but something in C3, the cell is blanked out even
though it shouldn't be.

So I need to change the conditions above with $B3 to reflect the condition
that it can be either _or_ both $B3 and/or $C3 that has a value whereas only
B3 is currently taken under consideration.

hth.

 

[KC Rippstein]

In your conditional format, select "formula is" and use an OR statement:
=OR($B3<>"",$C3<>"")
and this should accomplish what you desire.

However, I think conditional formatting is not the correct approach here.
You really should do one of the following instead:
1) Turn off zero values from being visible on your worksheet, or
2) Your balance column should be formatted in its entirety to change the
defaults to the bolding and/or colors you want, and the formula to calculate
the balance should just say:
=IF(OR($B3<>"",$C3<>""),$B3-$C3,"")

 

[StargateFanFromWork]

In your conditional format, select "formula is" and use an OR statement:
=OR($B3<>"",$C3<>"")
and this should accomplish what you desire.

It did, thank you.

However, I think conditional formatting is not the correct approach here.
You really should do one of the following instead:
1) Turn off zero values from being visible on your worksheet, or
2) Your balance column should be formatted in its entirety to change the
defaults to the bolding and/or colors you want, and the formula to calculate
the balance should just say:
=IF(OR($B3<>"",$C3<>""),$B3-$C3,"")

Hmmm, sounds good but too complicated for me to figure out with my limited
knowledge. I'll keep this in mind, however, as I always appreciate advise
from people who know much more than I do. <g> I'll work with this as it is
for now and see if I can implement at some future date what you've pointed
out here. Thanks! D

 

[KC Rippstein]

Glad I could help. Don't forget to indicate that this post was helpful to you.

There is not anything really wrong with using conditional formatting, I just
wanted you to see that it isn't necessary for this situation. I will try to
explain a little better.

Let's say your Balance colum is column E.
Select E2 and change the font to bold, color to blue, whatever formatting
you were looking for.
In E2, type =IF(OR($B3<>"",$C3<>""),$B3-$C3,"") and hit the green check mark
next to the formula bar (this tells Excel to accept your formula but stay on
that cell).
With E2 still selected, put your mouse cursor over the little black box on
the lower right side of E2. This is called a fill handle, and when you put
your cursor over it, your cursor changes from the big white cross to a thin
black cross. Now click that fill handle and drag down as far as you need
(say, E99). Now your formula and all your formatting carry down.