Accommodating for empty cells in this formula?

[StargateFanFromWork]

I have a formula in cell H21, for example, reads like this:
=IF($G21<>"",($H20-$G21),"")

is there a way to adjust the formula so that an empty cell in G21 doesn't
give the #VALUE! in subsequent cells in column H?





Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for
any and all empty cells in A2 to A9. It no longer matters if any of the
cells are empty, the formula correctly gives the correct addition of A1
plust a sum of everything between A2 to A10 without any #VALUE! results.
Was hoping to have the formula above also be impervious to empty cells.

TIA. D

 

[Scott]

Try:

=IF($G21<>"",IF(ISNUMBER($G21),($H20-$G21),""),"")

 

[Scott]

Ugh, I hate thinking after I type. This will probably work better.

=IF(AND(ISNUMBER($G21),ISNUMBER($H20)),($H20-$G21),"")

Scott

 

[StargateFanFromWork]

<g> It works just great but on trying it out, I realized that I am such an
idiot. It's hard to describe what we're trying to do and I'm not getting
the results needed so obviously, I missed something. I'm now not getting a
running total in column H, which I now see I should have realized that I had
to say.

The old formula =IF($G21<>"",($H20-$G21),"") is in cell
H21.

If there is anything at all in G21, it will be subtracted from H20 to get
the result in H21.
Column H keeps a decreasing running total as the amounts in column G, if
there are any, are subtracted as one goes along.

The thing is that this is an unusual spreadsheet. I have vital information
that needs to go in column B which serves as a description for what is being
paid. But there isn't always a dollar value involved. When I use a cell in
column B yet no dollar amount goes in the corresponding cell in column G,
the pertinent cell in column H is still affected, however, either way. I
just did some dummy amounts to show what I'm trying to do.


Description (column B) $ (col. G)
Subtotals (column H)

Bill 1
$15.00 $255.81
Laundry money $ 2.50
$253.31
Bill 2
$20.00 $233.31
Reminder - next week ($7.50)
$233.31
Amount owing to Paula $7.50
$225.81
Ticket $5.00
$220.81


I'll use conditional formatting in the cells so that repetitions like the
cell $233.31 above beside the empty cell, doesn't show up. I know how to do
that, so that's okay. The great formula below does indeed adjust for empty
cells so that I don't get #VALUE! results but I lose the running subtotal.
I'm sure there's a way to handle this but it's beyond me. Hopefully it'll
be easy enough to figure out.

Thanks again for the formula below. I've put it in my XL2K tips folder.
D

 

[Scott]

If your H value is always going to be a number, there's no point in
testing it. So basically, you want the following formula:

=IF(ISNUMBER($G21),$H20-$G21, $H20)

Scott

 

[StargateFanFromWork]

PERFECT! This is amazing. I've needed this type of formula forever. I now
will be able to use this over and over again in future spreadsheets, I know.
I have a few that I can go back to to fix, too.

Thanks much! I really appreciate it. D