conditional formula to show "open" or "closed"

=G6=C6
is not sufficient to test for equality of floating point numbers; you need
=(G6-C6)=0
where the parentheses are critical; otherwise equality may be declared
dispite differences in the lowest order bits. There can be differences that
Excel will not display, given MS's decision not to display more than 15
significant digits.

If the cells are both numeric and nominally equal (but not equal per this
test), the OP should try either rounding both in his formula or testing for a
suitably small difference.

Jerry

"Biff" wrote:

> Ok, start troubleshooting.........
>
> Try these formulas:
>
> =G6=C6
> =ISNUMBER(G6)
> =ISNUMBER(C6)
> =LEN(G6)
> =LEN(C6)
>
> If G6=C6 and BOTH are numeric numbers the results of the first 3 formulas
> should be TRUE. The results of the last 2 formulas should be identical.
>
> Biff
>
> "SBS" <SBS@discussions.microsoft.com> wrote in message
> news:653E5E28-5D7F-4651-B9F1-4B66E21B3CAF@microsoft.com...
> > Thank you JMB and Bif,
> >
> > Whether I enter a non-equal or an equal number into G6, I only get the
> > "open" message. If the formula was working, I would expect to see a change
> > in
> > the A1 message (either open or closed) depending upon the actual number
> > entered into G6. I have checked that the cells are formated as number
> > instead
> > of text but my results still do not work.
> >
> > "Biff" wrote:
> >
> >> Hi!
> >>
> >> One way:
> >>
> >> =IF(AND(ISNUMBER(G6),G6=C6),"Closed","Open")
> >>
> >> > so far I have a non-working formula:
> >> > =IF(g6=C6,"closed","open")
> >>
> >> If G6=C6 and your formula does not work it's possible that your numbers
> >> are
> >> not numbers but are TEXT.
> >>
> >> Biff
> >>
> >> "SBS" <SBS@discussions.microsoft.com> wrote in message
> >> news:667C39E5-E85A-458A-B2B1-AD24773FD141@microsoft.com...
> >> > Hi,
> >> > I have been able to do a formula that provides a random number in a
> >> > cell
> >> > (C6). Upon entering a matching number in another cell (G6), I want to
> >> > have
> >> > a
> >> > message ("closed") appear in yet another cell (A1) or if a non-matching
> >> > number is entered the message in cell (A1) would be ("open").
> >> >
> >> > Am new to formulas and have not been able to figure out what to do from
> >> > the
> >> > office help instructions.
> >> >
> >> > so far I have a non-working formula:
> >> > =IF(g6=C6,"closed","open")
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>
>

>If the cells are both numeric and nominally equal

Nominally equal ?

I didn't know that equality had a gray area! <g>

Biff

"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:AFF3368D-BB35-4FB0-B2C4-6003F3024FE8@microsoft.com...
> =G6=C6
> is not sufficient to test for equality of floating point numbers; you need
> =(G6-C6)=0
> where the parentheses are critical; otherwise equality may be declared
> dispite differences in the lowest order bits. There can be differences
> that
> Excel will not display, given MS's decision not to display more than 15
> significant digits.
>
> If the cells are both numeric and nominally equal (but not equal per this
> test), the OP should try either rounding both in his formula or testing
> for a
> suitably small difference.
>
> Jerry
>
> "Biff" wrote:
>
>> Ok, start troubleshooting.........
>>
>> Try these formulas:
>>
>> =G6=C6
>> =ISNUMBER(G6)
>> =ISNUMBER(C6)
>> =LEN(G6)
>> =LEN(C6)
>>
>> If G6=C6 and BOTH are numeric numbers the results of the first 3 formulas
>> should be TRUE. The results of the last 2 formulas should be identical.
>>
>> Biff
>>
>> "SBS" <SBS@discussions.microsoft.com> wrote in message
>> news:653E5E28-5D7F-4651-B9F1-4B66E21B3CAF@microsoft.com...
>> > Thank you JMB and Bif,
>> >
>> > Whether I enter a non-equal or an equal number into G6, I only get the
>> > "open" message. If the formula was working, I would expect to see a
>> > change
>> > in
>> > the A1 message (either open or closed) depending upon the actual number
>> > entered into G6. I have checked that the cells are formated as number
>> > instead
>> > of text but my results still do not work.
>> >
>> > "Biff" wrote:
>> >
>> >> Hi!
>> >>
>> >> One way:
>> >>
>> >> =IF(AND(ISNUMBER(G6),G6=C6),"Closed","Open")
>> >>
>> >> > so far I have a non-working formula:
>> >> > =IF(g6=C6,"closed","open")
>> >>
>> >> If G6=C6 and your formula does not work it's possible that your
>> >> numbers
>> >> are
>> >> not numbers but are TEXT.
>> >>
>> >> Biff
>> >>
>> >> "SBS" <SBS@discussions.microsoft.com> wrote in message
>> >> news:667C39E5-E85A-458A-B2B1-AD24773FD141@microsoft.com...
>> >> > Hi,
>> >> > I have been able to do a formula that provides a random number in a
>> >> > cell
>> >> > (C6). Upon entering a matching number in another cell (G6), I want
>> >> > to
>> >> > have
>> >> > a
>> >> > message ("closed") appear in yet another cell (A1) or if a
>> >> > non-matching
>> >> > number is entered the message in cell (A1) would be ("open").
>> >> >
>> >> > Am new to formulas and have not been able to figure out what to do
>> >> > from
>> >> > the
>> >> > office help instructions.
>> >> >
>> >> > so far I have a non-working formula:
>> >> > =IF(g6=C6,"closed","open")
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>