excel vs Quattro

[ptonsparky]

I used Quattro Pro for years. New laptop has excel. I can concatenate a series of cells containing 1 or 0, then convert that to a number using bintonum function in Qpro. I can't find a similar set of functions with Excel. Any suggestions?

 

[GeorgeB]

I used Quattro Pro for years. New laptop has excel. I can concatenate a series of cells containing 1 or 0, then convert that to a number using bintonum function in Qpro. I can't find a similar set of functions with Excel. Any suggestions?

I don't know which you are missing; from Help,

CONCATENATE (text1,text2,...)

Text1, text2, ... are 1 to 30 text items to be joined into a single text item. The text items can be text strings, numbers, or single-cell references.

Remarks

The "&" operator can be used instead of CONCATENATE to join text items.

And BIN2DEC converts to a decimal number ... there is also conversion to octal number and hex number if that is what you want. I've forgotten how QPRO handled those ... it's been too many years.

 

[ptonsparky]

I can't get the concatenation results of Excel to convert via the bin2dec. I get #NUM! for a result. Not life threatening, just frustrating.

 

[gadfly56]

I used Quattro Pro for years. New laptop has excel. I can concatenate a series of cells containing 1 or 0, then convert that to a number using bintonum function in Qpro. I can't find a similar set of functions with Excel. Any suggestions?

What George said. It looks like this:

=BIN2DEC(CONCATENATE(B1,C1,D1,E1,F1,G1))

I tried it out and it works. You may have to use the "Add-Ins" function in the "Tools" menu (I have Excel 2000) to add the Analysis ToolPak in order to access the function.

 

[gadfly56]

I can't get the concatenation results of Excel to convert via the bin2dec. I get #NUM! for a result. Not life threatening, just frustrating.

If you have more than 10 digits in the string, that's exactly what will happen. Also, it appears that the 10th digit is for sign + or -.

From Excel Help:

BIN2DEC(number)

Number is the binary number you want to convert. Number cannot contain more than 10 characters (10 bits). The most significant bit of number is the sign bit. The remaining 9 bits are magnitude bits. Negative numbers are represented using two's-complement notation.

Remark

If number is not a valid binary number, or if number contains more than 10 characters (10 bits), BIN2DEC returns the #NUM! error value.

Examples

BIN2DEC(1100100) equals 100

BIN2DEC(1111111111) equals -1

 

[ptonsparky]

You got it. Any way around this? I use it when working with PLCs.

 

[gadfly56]

You got it. Any way around this? I use it when working with PLCs.

Assuming you don't need to worry about negative numbers, use the CONCATENATE and BIN2DEC functions on the first nine (9) entries in your table. Repeat for the next nine (9) entries and multiply the the second result by 512 and add the two together. It's the same as shifting the decimal place for base 10 math.

 

[ptonsparky]

Boy they made that simple. :roll:

I've been doing it with a 2 step process for I don't know how many years, now I get the best ??, or at least the latest, and have to jump through hoops. And new hoops to boot.

Don't think I will buy Excel for the other sytems.

Thanks.

 

[GeorgeB]

Boy they made that simple. :roll:

I've been doing it with a 2 step process for I don't know how many years, now I get the best ??, or at least the latest, and have to jump through hoops. And new hoops to boot.

Don't think I will buy Excel for the other systems.

Thanks.

How many bits do you have? If 16, the splitting into 2 groups of 8 which will give you 0-255 for each, multiplying the more significant group by 256 then adding the less significant group will do ... yielding a number from 0-65535. Let's assume 16 bits from A1 to P1 ... =256*BIN2DEC(CONCATENATE(A1,B1,C1,D1,E1,F1,G1,H1))*=BIN2DEC(CONCATENATE(I1,J1,K1,L1,M1,N1,O1,P1))

You only have to write the formula once ...

 

[gadfly56]

How many bits do you have? If 16, the splitting into 2 groups of 8 which will give you 0-255 for each, multiplying the more significant group by 256 then adding the less significant group will do ... yielding a number from 0-65535. Let's assume 16 bits from A1 to P1 ... =256*BIN2DEC(CONCATENATE(A1,B1,C1,D1,E1,F1,G1,H1))*=BIN2DEC(CONCATENATE(I1,J1,K1,L1,M1,N1,O1,P1))

You only have to write the formula once ...

Did you mean =256*BIN2DEC(CONCATENATE(A1,B1,C1,D1,E1,F1,G1,H1))+BIN2DEC(CONCATENATE(I1,J1,K1,L1,M1,N1,O1,P1))?

 

[ptonsparky]

Ok. Good to go. Thanks

 

[dbuckley]

have a look here: http://sulprobil.com/html/longdec2bin.html

 

[ptonsparky]

That certainly cleared things up.

 

[GeorgeB]

Did you mean ...

What do you mean I'm supposed to proofread my work? Oh, the brakes failed from a software error? Of course you are correct!