# Binary (concatenated From Decimal) To Hex Conversion Error

Feb 18, 2014

I am taking a decimal degree (longitude) and converting it to binary then to hexadecimal. The value I am using is a Binary Angle 32 bit structure that will be translated into an 4-byte hexadecimal (with two characters per byte i.e. "20" is one byte and results in spaces).

This is what I have:

Input of 359.999999916 (in cell A2)
Cells B2 through AG2 I have converted A2 into binary (using formulas like "=IF(A2-180>0,1,0)" for cell B2, "=IF(A2-180*B2-90>0,1,0)" for cell C2, "=IF(A2-180*B2-90*C2-45>0,1,0)" for cell D2, and so on)

I then concatenated all of the results into one cell using the following formula:

To get a result of (located in cell A4):
11111111111111111111111111111110

However, when I attempted to use the BIN2HEX conversion on A4, I get a "NUM!" error. Here is the formula that I was using in cell A5:
=BIN2HEX(A4,8)

## Decimal To Binary?

Oct 27, 2007

Does anyone know how to convert a 4 digit number to binary in excel? I used the =DEC2BIN formula, but it only works on a 3 digit number that is smaller than 512.

## Convert Decimal To 13 Bit Binary

Aug 11, 2008

I was reading this Binary Conversion from Decimal on this site, however i am unable to reply to the thread. This code is exactly what i was looking for but have the need for it to be able to give the result as a number of bits eg. i would like the result to be dispalyed as 13 bits. I have tried modifying the code without any success and would like to be able to use it in this form. Public Function Dec2Base( Num As Long, base As Long, length as long) As String

## Convert Binary Info In A Column To Decimal?

Mar 17, 2014

I am trying to convert 14 to 15 digits lengthy Binary value to number.

The formula =BIN2DEC(A2) is giving error #NUM

101111100001010
101111100001010
101111100001010
101111000011100
101111000011100
101111000011100
110001000001010

## Format Cell With Concatenated Contents To 2 Decimal Places

Dec 3, 2013

I have a list of cells that has two values concatenated. The first I would like to round to 2 decimal places, and the second is always a integer

Consider this formula..

Code:
=IF(SUMIFS('Analysis Data'!\$I\$5:\$I\$1840,'Analysis Data'!\$C\$5:\$C\$1840,3,'Analysis Data'!\$E\$5:\$E\$1840,1)=0,0,SUMIFS('Analysis Data'!\$I\$5:\$I\$1840,'Analysis Data'!\$C\$5:\$C\$1840,3,'Analysis Data'!\$E\$5:\$E\$1840,1)/COUNTIFS('Analysis Data'!\$C\$5:\$C\$1840,3,'Analysis Data'!\$E\$5:\$E\$1840,1,'Analysis Data'!\$I\$5:\$I\$1840,">0"))
&"("&COUNTIFS('Analysis Data'!\$C\$5:\$C\$1840,3,'Analysis Data'!\$E\$5:\$E\$1840,1,'Analysis Data'!\$I\$5:\$I\$1840,">0")&")"

In one cell this yields the value;

2.42553191489362(47)

In another it yields 2.975(40)

and so on..

I would like to format it to display as 2.43(47) and 2.98(40)

FWIW this means average of 2.43 games refereed per session in 47 sessions of refereeing.

## Fractions To Decimal Conversion

Dec 14, 2009

I have received help on this topic in the past and I though I had solved the issue, however I realized recently that my formula will not work on any fractions larger than 1 inch. I am converting machine threads in fraction form to a decimal equivalent. here is an example of the what the entry looks like before it is converted.

ex,
1/2-20 3A (becomes .5000)or
3/4-13 2A (becomes .7500)or
1-14 3A this one will not work with my current formula (should be 1.0000);

## Bond Price Conversion - Decimal Form

Jan 4, 2007

I have a list with prices of US Treasury bonds. They are quoted in a partcular way that I would like to convert to decimal form. They are quoted like this eg: 99-02. Now this means that the price is 99 and some decimal. The two first numbers are the number of 32:s. Thus in this case the price is 99 and 2/32. However if there are three decimals eg 99-021 the last digit refers to the number of 124:s. Thus the price is 99 and 2/32 and 1/256.

Sometimes There are only two decimals followed by a plus or minus sign eg 99-30+ or 99-10-. The plus/minus indicates that you should add/subtract 1/64. (This is the correct way, I know it seems strange but that is the way it is). Also number of digits before the line (in the example 99 might be one, two or three, eg it can look like this: 5-01, 99-021 or 100-21). Is there any way that I can convert these price quotes into decimal form using worksheet functions?

## Abnormal Decimal Conversion: Auto-import Statistics From The Web Via Web Query

May 9, 2009

I am auto-importing statistics from the web via web query. This information imported has a number in the form of a decimal stated as either x.1, or x.2. x.1 represents x-1/3 (one-third) and x.2 represents x-2/3 (two-thirds). I need to use these numbers in a calculation after importing them, but using x.1 or x.2 obviously does not give me accurate results as the numbers should actually be x.3333333 and x.6666666 respectively. What is the easiest way to convert the imported numbers to their actual decimals?...

## Paste Two Decimal Number In Excel Without Extra Decimal Places Appearing

Aug 13, 2009

I have a vba macro that takes data from one workbook and pastes it into another workbook. In doing this I have declared a few variables of type single (I only need two decimal precision). However, when I copy the values from the cells on the source workbook and paste them into the target workbook, the numbers end up having 12 decimal places. Ultimately, this extra precision causes my totals to be off by .01 or more after a while. I have tried rounding the number as I pull it off the source workbook into the variable, but that didn't matter. How do I solve this problem? Code for pulling data from source workbook:...

## Formatting Group Of Cells To Have Different Decimal Rules Based On Decimal Value

Aug 3, 2006

Basically, I want to format a group of cells to display 1 decimal figure if the number is not a whole number. If the number is a whole number (or if the rounded first decimal place is 0) I want it to display no decimal.

## Removing Decimal Point While Maintaining # Of Decimal Places

Jun 10, 2009

I need to convert a column of numbers currently formatted with 2 decimal places e.g. 112.12 to 4 decimal places (without the decimal point). I need the end result to be 1121200. I've tried a few different suggestions given on the forum previously but can't seem to retain the 4 decimal places that I require.

## Conversions Of Decimal Feet To Decimal Inches - Formula?

Apr 15, 2012

I have to make a excel document in which I have length and width in feet and inch format.

E.g. 10.01 in which 10 is feet and .01 is inch

I have all the length and width values in the above format. And what I want to do is convert the inch value (10.01) to feet value (.01=.08 feet) .

Just like the calculator here does.. [URL] ........

Like
10.01=10.08
10.02=10.16
10..03=10.25
...and so on...

Here is table of conversions from inches to decimal feet. But I don't know how to get a formula for this in excel...????

Inch Decimal of a Foot
1 inch 0.0833
2 inches 0.167
3 inches 0.250
4 inches 0.333
5 inches 0.417
6 inches 0.500
7 inches 0.583
8 inches 0.667
9 inches 0.750
10 inches 0.833
11 inches 0.917
12 inches 1.000

## Convert Or Format Decimal To X Digits Without Decimal Point

Aug 8, 2008

I am trying to create a unique sample code by putting together the values of other cells that a user will input. It's all working well apart from the last part, where I am trying to include a decimal number. I want the decimal number to appear without the central "." and in a four digit format. e.g. 2.5 would appear as 0250, 14.25 would appear as 1425. This is the formlua I am using currently:

=IF(ISBLANK(B4),"",IF(LEFT(C4,1)="w",(B4&"-"&TEXT(F4,"YYMMDD")&C4&TEXT(G4,"HHMM")),(B4&"-"&TEXT(F4,"YYMMDD")&C4&LEFT(TEXT(H4,"00"),2)&RIGHT(TEXT(H4,"00"),2))))

However, where the value of H4 is 2.5, I am getting a result of 0303 (I've put this part in bold). I have attached a small spreadsheet to aid understanding.

## Calculate Decimal Portion Of Decimal Number`

Jun 10, 2007

I need a formula to multiply only the decimal number in a cell and not the integer. For example: the number in the cell is 57.3615. I want to multiply .3615 only.

## Floating Binary In Subtraction

Oct 13, 2008

cell A1 contains the numeric value: 3961.3
cell A2 contains the follwing: ="z equals "&B3-3933.7

which i would expect to give me: "z equals 27.6"

BUT, it gives me: "z equals 27.600000004"

which is awefully strange becos the 4 comes form nowhere...

what makes some sort of sense is that if the value in A1 is changed to 3961.4 or 3961.5, then the final decimal 4 in A2 changes into a 3, and then a 2.

when the value in A1 is changed to xxxx.7 the number in cell A2 becomes what one would expect it to be.

how do i get rid of these strange decimals???

(formatting cells, setting number of significant figures does not work. i naturally already tried that)

## Export To Binary Format

Sep 5, 2009

Is there anyway to convert excel sheet into a file with binary format?

Or maybe export them into binary files?

I want to change my database type to binary from text..

Problem is my database is Huge, it's a 1044x1044 matrix..

The previous .txt database is exported using macro, into 1044 files that contain 1044 values each. I want to do the same except with binary format...

## Compare The Binary Sequences

Nov 18, 2008

I have 70 sequences of binary coded variables each, which I would like to compare in terms of overlaps for the number "1", e.g.,

a1 1 0 0 0 0 1
a2 0 1 1 1 0 0
a3 0 1 1 0 1 0
.
.
.
a70 0 1 0 0 0 0

How can I do a pairwise comparison in Excel for the number "1" (ie how often does the number "1" occur at the same place for two sequences?).

I thought about a correlation matrix (that compares sequences a1-> a2, a1-> a3, a1->a4, ...., a1-> a70; a2->a3, a2-> a4, ...., a2-> a70; .....; a69-> a70), but am looking for an easy way to compute this in Excel.

Jun 16, 2006

I am running into one of the strangest issues I have seen in a long time. I have a user defined type that I utilize in VB6 and write out to a binary file....

## Excel 2007 :: Convert Hex To Binary?

Feb 7, 2012

I want to convert Hex to Binary. I want the Binary in 8 different cells.The hex input would be lets say A1 I would want to convert that to eight different cells Bit7 G10, Bit6 G12, Bit5 G14, Bit4 G16, Bit3 E10, Bit2 E12, Bit1 E14 and Bit0 E16.

Then I want another Hex input on A2 and convert that to eight diferent cells.

Bit7 K10, Bit6 K12, Bit5 K14, Bit4 K16, Bit3 I10, Bit2 I12, Bit1 I14 and Bit0 I16.

How would I do This?

## How To Speed Up Binary File Read

Apr 9, 2014

I have written a little test sub which reads the contents of a binary file, which is approximately 0.2 Mb in size, into a byte array.

My sub is as follows:

[Code]...

It works just fine, BUT, it is incredibly slow (it spends 80 seconds in the For loop!!!!!)

How can I speed this up?

Note that the binary file can be of arbitrary size.

I tried using a string buffer filled with zeros, and it works!

## Working With Data In Binary Format?

Jun 15, 2009

How well does Excel / VBA deal with data in a binary format? I am working on a Base64 decoder, which in basic terms means I will have 4 numbers, which I need to convert into their binary representations (6 bits each). I then need to combine these binary groups together, and then divide them up differently to give me 3 numbers (made of 8 bits each).I'm just wondering if anyone has any examples on how best to work with binary in Excel? Also if there are an gems of builtin library functions?

## Formula For Binary To Decimial Not Working Right

Mar 11, 2009

this formula was working great since 12-08, but someone came to me yesterday saying there is a flaw in the formula between Company A and Company B

If you note that 10.125.239.0/24 is overlapped with 10.125.236.0/22, yet is marked FALSE ...

## Large Hexadecimal To Binary Function

Jun 9, 2007

I read the thread Converting from Hexadecimal to Binary and what I need to do is convert the following hex number to binary; 9E6799CF. The function in that thread doesn't seem to handle that large of a hex value. Is there anyway of doing that with a HexToBin function, short of chopping it up converting the smaller numbers and recombining it in binary?

## Copy Data From CSV Into Next Empty Row In Binary Tracker

Jul 9, 2014

I have a lot of outputs being spat out in csv files.

I need to get some of the data from set columns and copy this over into a master tracker. Columns are not sequencial and may need to copy change at a later date. Example attached.

Some data fields will have letters in them as well. Some are varying in length in terms of the amount of data within a cell. eg. "AB389238923589Y234HI" or just "A-GT6"

I don't know where to start VBA wise but it must be possible rather than open copy paste.

The tracker has a set name but will change quaterly.

The Output CSV files are new files with a number (no date) for titles.

## Excel 2010 :: Graphing Binary Data

Dec 10, 2012

How do I graph this data? I am plotting the test results (Column A is the employee number, B is the Employee name, then each of the questions are Columns D through DX with DY being the total correct by each employee) (Rows are employees and then the Ratio that the question was answered correctly) The goal is to find questions that are routinely answered in correct or that have a problem with the question.

I was hoping to graph the correct and incorrect questions so those that are either high frequency correct, incorrect or not answerd identified I can improve the test.

Example of the spreadsheet. I am using =COUNTIF(D3:D15,"1")/12 for the ratio answered correct - this should also see the ones not answered as incorrect. 1 = correct answer 0 = incorrcet null=not answered

NO
Employee Name
Question
1
2
3

[Code]...

How can I graph these results to answer which questions have a high frequency of incorrect answers? I am thinking some kind of scatter plot.

## Create Bar Chart With Ordered Binary Data?

May 28, 2013

I want to create the chart below. I have data that is coded with a binary flag (0 = red, 1 = blue) that is also ordered. For example, the data I would use to hypothetically create the left hand column below looks like this

Binary Flag
Rank
0
1

0
2

1
3

0
4

1
5

1
6

## Cell Selection And Data Entry In Binary Matrix

Jul 4, 2013

I've set up a binary matrix, such that I have 421 unique headings repeated twice: once in each column and once in each row (i.e., in the matrix the headings for row 5 and column 5 will trace back to the same description in the column headings and in the row headings).

I need to fill in 1s (case satisfied) or 0s (not satisfied) in this 421X421 matrix. Where my problem lies is in finding the exact row-column cell combination in which I need to enter the 1s. Descriptions that belong to the same group or category will be marked by these 1s in the matrix. For example, if I have descriptions 5, 9, 13, and 17 belonging to the same group I need to enter a 1 in each possible combination of these numbers: {5,9} {5,13} {5,17} {9,13} {9,17} {13,17}. I need a quick formula that will compute all possible combinations for inputted descriptions AND will enter a 1 into the cells, which correspond to these combinations.

## Averaging Binary Values Over Continuous / Non-Uniform Domain

Aug 7, 2013

I have a very large dataset (~5000 points in a 2D array) which I need to plot and fit a linear trendline to. There are 90 subjects each of which has a daily binary event checked over its lifespan. eg:

Day: 1 2 3 4 5 6 7 8 9 10
subj 1 0 1 0 0 0 (dead)
subj 2 1 1 1 0 1 0 1 0 0 0(dead)
subj 3 0 0 1 0(dead)

I simply averaged the number of events per day per subject and plotted them but near the long-lived end of the data the variability gets really high. I have normalized the lifespan for each subject such that instead of the event occurring on day 10, it occurred at 0.2 of its total lifespan (dying on day 50). Now that my data is in this format, I want to create an average for all subjects over the domain 0-1 representing birth-death.

The trouble is that now each subject's domain has different intervals between points (eg. 0.1 for subjects living 10 days and 0.006993 for those living 143 days). How to now space out the cells (somewhat) automatically so I can average the columns? My array is 90 x 150 so doing it manually is not really possible. Or, alternatively, is there a way to create a continuous average rather than a discrete one? Some sort of function that goes through each infinitely small (or even a discrete really tiny interval) point of the domain (0-1) and averages the binary y-value?

## Doing IF Compare Against Concatenated Value In VBA

Jan 31, 2013

struggling with the syntax of this one. Lets say we have a variable called MYWEEK with a value of WEEK 9 and cells A1 and A2 have values of 9 and 10 respectively. I need to be able to compare the variable against cells A1 or A2 but with a prefix of WEEK .

I've tried the following:

if MYWEEK = "WEEK " & A1 then ...
if MYWEEK = "WEEK " + A1 then ...
if MYWEEK = ("WEEK " & A1) then ...

I know the answer is staring me in the face but I cant see it. Any use of the ampersand presumably invalidates the IF statement by introducing multiple conditions rather than a concatenated value. How do you compare a value against a concatenated set of criteria in VBA?

## Concatenated TextBox

Oct 30, 2007

I have a TextBox that I want to show the concatenated value of two cells. Is that possible? Or do I need to concatenate it on the sheet and then have the TextBox = that cell?