conditional formula to show "open" or "closed"

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")

I don't see any problem w/your formula. Are you getting an error or just an
incorrect result? Are you sure G6=C6? Try increasing the number of decimals
in each cell to be sure you don't have any rounding issues. You may have to
incorporate the Round function in your formula if this is the case.



"SBS" wrote:

> 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")
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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")
>
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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")
> >
> >
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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")
>> >
>> >
>> >
>>
>>
>>

=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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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:[email protected]...
> =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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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")
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>