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

Try:

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


StargateFanFromWork wrote:
> 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

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

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

Scott

StargateFanFromWork wrote:
> 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

<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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Ugh, I hate thinking after I type. This will probably work better.
>
> =IF(AND(ISNUMBER($G21),ISNUMBER($H20)),($H20-$G21),"")
>
> Scott
>
> StargateFanFromWork wrote:
> > 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

:-)

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 wrote:
> <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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Ugh, I hate thinking after I type. This will probably work better.
> >
> > =IF(AND(ISNUMBER($G21),ISNUMBER($H20)),($H20-$G21),"")
> >
> > Scott
> >
> > StargateFanFromWork wrote:
> > > 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

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

"Scott" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> :-)
>
> 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 wrote:
> > <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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > Ugh, I hate thinking after I type. This will probably work better.
> > >
> > > =IF(AND(ISNUMBER($G21),ISNUMBER($H20)),($H20-$G21),"")
> > >
> > > Scott
> > >
> > > StargateFanFromWork wrote:
> > > > 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