conditional formula to show "open" or "closed"

S

SBS

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

JMB

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.
 
B

Biff

Hi!

One way:

=IF(AND(ISNUMBER(G6),G6=C6),"Closed","Open")
so far I have a non-working formula:
=IF(g6=C6,"closed","open")
Click to expand...

If G6=C6 and your formula does not work it's possible that your numbers are
not numbers but are TEXT.

Biff
 
S

SBS

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.
 
B

Biff

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
 
J

Jerry W. Lewis

=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
 
B

Biff

If the cells are both numeric and nominally equal

Nominally equal ?

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

Biff
 
Top