## Convert two 16bit values to float in excel

If you are ever trying to convert two modbus 16-bit words to a float in excel here is the code to do it.

Its based on the code I found here:

Link

Function convert_to_float(top As Long, bottom As Long) Dim number As Long number = top * (2 ^ 16) number = number + bottom Dim n As Single 'this is the resulting single number Dim frac As Double 'fractional binary number from significant Dim e As Integer 'exponent Dim sig As Long 'significant integer Dim i As Integer 'for for loops and whatnot Dim sign As Integer 'get exponent e = Int(number / (2 ^ 23)) 'shifts the bit down sign = e And 256 e = e And 255 e = e - 127 'e is biased by 127 so it can stay unsigned (always positive) sig = number And 8388607 ' to get lower 23 bits frac = 1 'start with 1 so result is 1. If e = -127 Then 'denormalized (0.fraction rather than 1.fraction) e = -126 frac = 0 'start with 0 so result is 0.. End If 'calculate binary fraction from significant. For i = 22 To 0 Step -1 'step through all 23 bits. If (sig And 2 ^ i) > 0 Then frac = frac + 2 ^ -(23 - i) 'if the bit is a 1, the number will be non zero. Next n = (2 ^ e) * frac 'calculate the final number If (number And -2147483648#) > 0 Then n = -n 'if sign bit is set, make negative. convert_to_float = n 'return result End Function |

Categories: Uncategorized

## Recent Comments