What 'value' is a Dec2Bin output?

[EvilTony]

I'm trying to verify that the guess someone makes as to the binary equivalent
of a decimal number is correct.

Example

cell A1 holds, say, 8

User must enter their 'estimate' of the binary value of 8 in cell B1

cell C1 formula: =IF(DEC2BIN(A1)=B1,"Yes","No")

I cannot get this to return a "Yes" when B1 = 1000

----

Strangely, what does work is if I use the 'reverse'
cell c1 formula: =IF(BIN2DEC(B1)=A1,"Yes","No")

Why doesn't Excel 'see' an entry of 1000 as being the same as an putput of
1000 from Dec2Bin?

 

[Jacob Skaria]

Try

=IF(DELTA(DEC2BIN(A1),B1),"Yes","No")

If this post helps click Yes

 

[JLatham]

You need to get them both to either numbers or text. Here's one way to turn
both into 8-character text strings to compare:
=IF(TEXT(DEC2BIN(A1),"00000000")=TEXT(B1,"00000000"),"YES","NO")

 

[EvilTony]

Try

=IF(DELTA(DEC2BIN(A1),B1),"Yes","No")

Thanks.
Thanks to all of you.

As Jakob's "Delta" function is immediately and plainly explained in the Help
pages, that's the one I'll be going with.

My students would not immediately appreciate the use of "text" with a
number, nor the idea of adding something (even a zero) to a number being
checked for correspondence.

Once again, thanks.

 

[Mike H]

Hi,

I agree Jacob's solution is very good but multiplication by 1 or the
addition of zero is a commonly used technique in Excel for converting text to
numbers.

If you read Excel help again for Delta I think you still have some
explaining to do to your students. For example

A1=8
A2 =DEC2BIN(A1)
A3 =isnumber(A2) evaluates as FALSE
A4 = istext(A2) evaluates as TRUE

So we have established that DEC2BIN returns text and if we now turn to Excel
help

Remarks

If number1 is nonnumeric, DELTA returns the #VALUE! error value.
If number2 is nonnumeric, DELTA returns the #VALUE! error value.

Yet delta works!! Not very intuative for your students.

Mike

 

[Jacob Skaria]

Totally agree with Mike.. Multiplication by 1/Addition of 0 is a commonly
used by programmers in most scripting languages for type casting.

If this post helps click Yes

 

[Jerry W. Lewis]

Put it in quotes; "1000"

If the issue is that it is calculated, then you can coerce it to text, as in
=calc&""

Alternately, you can coerce Dec2Bin(8) to a number, as in
=Dec3Bin(8)+0

Jerry

 

[Mike H]

Jacob,,

And the reason I suggested it instead of DELTA is because I have some
difficulty with DELTA because of what I see as inconsistencies. Consider this

A1 to A3 hold the values 2.1, 2.3, 2.4
B1 to b3 hold the values 2.1, 2.4, 2.3

=SUM(A1:A3)=SUM(B1:B3) - Evaluates as we might expect as TRUE

but
=DELTA(SUM(A1:A3),SUM(B1:B3))=1 - Evaluates as FALSE so DELTA doesn't
recognise the 2 sums as equal.

Mike

 

[Jacob Skaria]

When you work with 2003 make sure to add the Add-In

If this post helps click Yes

 

[Bernd P]

Hello,

Ok, we can compare them via
DELTA(DEC2BIN(8),1000)
EXACT(DEC2BIN(8),1000)
--DEC2BIN(8)=1000
DEC2BIN(8)=1000&""
but I would prefer one of the later two since its never too early to
learn about type differences and how it should be compared with Excel.

Regards,
Bernd