Reporting of Empty Cells

[Dennis]

I have created a formula that requires reference cells to not report back "0"
unless an entry is made into the cell, including "0". I attempted to use the
#N/A function, but since there are four or more cells that contribute to the
formula, nothing is returned until all cells have received an entry.

The formula I am using is:

=IF(H26=O26,"1",IF(H26>O26,"0",IF(H26<O26,"0")))

If the cell is read as "0" for both H26 and O26, the result is "1". What I
need is the result to be "0", until an actual entry is made in H26 & O26
since the entry could be "0" as well as a whole number.

Is there any way to reset the reference cell so that "0" is not read in the
formula unless entered?

[MSP77079]

Try this:

=IF(H26<>"", IF(O26<>"",IF(H26=O26,1,0), 0),0)

- Pete

[Biff]

Hi!

Not sure of what you're asking but here's some
observations on your formula:

>=IF(H26=O26,"1",IF(H26>O26,"0",IF(H26<O26,"0")))

"1" and "0" will be treated as TEXT, not numbers. If you
actually mean for those values to be NUMBERS leave out the
quotes. You only want to quote TEXT. (although there are
cases when you just might want numbers as TEXT)

>IF(H26>O26,"0",IF(H26<O26,"0")))

This is somewhat contradictory but if it's intentionally
set to be that way you can express that in this manner:

H26<>O26

Maybe this is what you're after:

=IF(AND(ISBLANK(H26),ISBLANK(O26)),"",IF(H26<>O26,0,1))

Biff

>-----Original Message-----
>I have created a formula that requires reference cells to
not report back "0"
>unless an entry is made into the cell, including "0". I
attempted to use the
>#N/A function, but since there are four or more cells
that contribute to the
>formula, nothing is returned until all cells have
received an entry.
>
>The formula I am using is:
>
>=IF(H26=O26,"1",IF(H26>O26,"0",IF(H26<O26,"0")))
>
>If the cell is read as "0" for both H26 and O26, the
result is "1". What I
>need is the result to be "0", until an actual entry is
made in H26 & O26
>since the entry could be "0" as well as a whole number.
>
>Is there any way to reset the reference cell so that "0"
is not read in the
>formula unless entered?
>.
>

[Biff]

Hmmm...

Maybe this is better now that I've tinkered with it:

=IF(OR(ISBLANK(H26),ISBLANK(O26)),"",IF(H26<>O26,0,1))

Biff

>-----Original Message-----
>Hi!
>
>Not sure of what you're asking but here's some
>observations on your formula:
>
>>=IF(H26=O26,"1",IF(H26>O26,"0",IF(H26<O26,"0")))
>
>"1" and "0" will be treated as TEXT, not numbers. If you
>actually mean for those values to be NUMBERS leave out
the
>quotes. You only want to quote TEXT. (although there are
>cases when you just might want numbers as TEXT)
>
>>IF(H26>O26,"0",IF(H26<O26,"0")))
>
>This is somewhat contradictory but if it's intentionally
>set to be that way you can express that in this manner:
>
>H26<>O26
>
>Maybe this is what you're after:
>
>=IF(AND(ISBLANK(H26),ISBLANK(O26)),"",IF(H26<>O26,0,1))
>
>Biff
>
>>-----Original Message-----
>>I have created a formula that requires reference cells
to
>not report back "0"
>>unless an entry is made into the cell, including "0". I
>attempted to use the
>>#N/A function, but since there are four or more cells
>that contribute to the
>>formula, nothing is returned until all cells have
>received an entry.
>>
>>The formula I am using is:
>>
>>=IF(H26=O26,"1",IF(H26>O26,"0",IF(H26<O26,"0")))
>>
>>If the cell is read as "0" for both H26 and O26, the
>result is "1". What I
>>need is the result to be "0", until an actual entry is
>made in H26 & O26
>>since the entry could be "0" as well as a whole number.
>>
>>Is there any way to reset the reference cell so that "0"
>is not read in the
>>formula unless entered?
>>.
>>
>.
>

[Ragdyer]

If I understand what you're looking for, try this:

=IF(OR(H26<>O26,AND(H26="",O26="")),0,1)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Dennis" <[email protected]> wrote in message
news:[email protected]...
> I have created a formula that requires reference cells to not report back
"0"
> unless an entry is made into the cell, including "0". I attempted to use
the
> #N/A function, but since there are four or more cells that contribute to
the
> formula, nothing is returned until all cells have received an entry.
>
> The formula I am using is:
>
> =IF(H26=O26,"1",IF(H26>O26,"0",IF(H26<O26,"0")))
>
> If the cell is read as "0" for both H26 and O26, the result is "1". What
I
> need is the result to be "0", until an actual entry is made in H26 & O26
> since the entry could be "0" as well as a whole number.
>
> Is there any way to reset the reference cell so that "0" is not read in
the
> formula unless entered?

[Dennis]

Yes, this seems to work perfectly and the cells report exactly what I am
looking for.

Thank you for your assistance!

Dennis

"Ragdyer" wrote:

> If I understand what you're looking for, try this:
>
> =IF(OR(H26<>O26,AND(H26="",O26="")),0,1)
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
>
> "Dennis" <[email protected]> wrote in message
> news:[email protected]...
> > I have created a formula that requires reference cells to not report back
> "0"
> > unless an entry is made into the cell, including "0". I attempted to use
> the
> > #N/A function, but since there are four or more cells that contribute to
> the
> > formula, nothing is returned until all cells have received an entry.
> >
> > The formula I am using is:
> >
> > =IF(H26=O26,"1",IF(H26>O26,"0",IF(H26<O26,"0")))
> >
> > If the cell is read as "0" for both H26 and O26, the result is "1". What
> I
> > need is the result to be "0", until an actual entry is made in H26 & O26
> > since the entry could be "0" as well as a whole number.
> >
> > Is there any way to reset the reference cell so that "0" is not read in
> the
> > formula unless entered?
>
>

[Ragdyer]

Thanks for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dennis" <[email protected]> wrote in message
news:[email protected]...
> Yes, this seems to work perfectly and the cells report exactly what I am
> looking for.
>
> Thank you for your assistance!
>
> Dennis
>
> "Ragdyer" wrote:
>
> > If I understand what you're looking for, try this:
> >
> > =IF(OR(H26<>O26,AND(H26="",O26="")),0,1)
> > --
> > HTH,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
-
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
-
> >
> > "Dennis" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have created a formula that requires reference cells to not report
back
> > "0"
> > > unless an entry is made into the cell, including "0". I attempted to
use
> > the
> > > #N/A function, but since there are four or more cells that contribute
to
> > the
> > > formula, nothing is returned until all cells have received an entry.
> > >
> > > The formula I am using is:
> > >
> > > =IF(H26=O26,"1",IF(H26>O26,"0",IF(H26<O26,"0")))
> > >
> > > If the cell is read as "0" for both H26 and O26, the result is "1".
What
> > I
> > > need is the result to be "0", until an actual entry is made in H26 &
O26
> > > since the entry could be "0" as well as a whole number.
> > >
> > > Is there any way to reset the reference cell so that "0" is not read
in
> > the
> > > formula unless entered?
> >
> >