Hi all,
This may be an easy one and I'm just not understanding formatting...
I have calculations which give numbers like:
0.037999999
9.899999619
4.900000005
0.349999994
I'll be printing (or writing) data to a file using vba
But the numbers I want are:
0.038
9.9
4.9
0.35
Is there an obvious way to do this? I note when the numbers are in columns then the values I want show properly unless I widen the column. So Excel is doing some internal formatting in that case.
Thanks in advance!
-Jim
How are you writing to a file? What type of file are you writing to?
The difference between what you have and what you say you want looks like rounding. What you have may even be effected by "floating point errors". My first thought would be to nest whatever your current calculations are into a ROUND() function. =ROUND(current calculation,number of digits).
Another possibility, since all of your numbers seem to be 2 significant figures, would be to pass them through a "scientific" text string =TEXT(current calculation,"0.0E+0"). If you are outputting to a text type file (.txt or .csv), and the program that needs to read this text file can read scientific format, then you can simply output in scientific format and let the next program read it like that. If you need to convert back to number from "numbers stored as text", nest the TEXT function inside of a VALUE() function =VALUE(TEXT(current formula,"0.0E+0")).
Some of this depends on exactly what is happening and exactly what you need to happen. Can you provide additional context for this task?
Originally Posted by shg
What I'm doing is taking the 32 bit representation of a decimal and converting it to it's base 10 representation.
For example, take a binary number like
0 0 1 1 1 1 0 0 1 0 0 1 1 0 1 1 1 0 1 0 0 1 0 1 1 1 1 0 0 0 1 1
s e e e e e e e e f f f f f f f f f f f f f f f f f f f f f f f
7 6 5 4 3 2 1 0-1-2-3-4-5-6-7-8-9...etc <--exponent of 2
s is the sign (0=positive, 1=negative)
I construct E = 0*2^7 + 1*2^6 + 1*2^5 + ... + 0*2^2 + 0*2^1 + 1*2^0
I construct F similarly, using the negative exponents.
Then, E=E-127 and F=1+F
Finally, my resulting value is val = F * 2^E.
For the binary above, you should get E = -6 and F = 1.215999961, and val = 0.018999999
I will output the results to a text file and the fractional part of the number can range from 0 to 8 digits long.
I've played around with ROUND, ROUNDUP, etc. I'm thinking there's possibly a way to format this so they round
appropriately when I print or write to the text file.
-Jim
The custom format 0.### will do what the OP asks.
How that is implimented (through cell formatting, the TEXT function or the VBA function Format) would depend on how the file is being exported.
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
It does for the examples I've given. It wouldn't work for, say, 0.50000299999. I'll keep exploring around. Maybe formatting the F value before finding val might work.
This arises from the old Fortran DECODE function, which I can't find any details on.
Thanks for the reply!
As mikerickson said, some of this will depend on exactly how the numbers are being exported to text. For example, if you were using the "save as -- csv or text" command to write the text file, I would probably format the cells as scientific with seven or eight significant digits ("0.000000E+0"). Looking at your example numbers, it appears that the eighth digit maybe is not significant. Seven digits would round each of those to the correct value, but the eighth digit does not. Assuming that whatever is reading this text file can read scientific notation (I have had good success with FORTRAN programs reading numbers as text in scientific notation), then that should work.
As for the old FORTRAN function, this was the first result from an internet search for "fortran decode function": https://docs.oracle.com/cd/E19957-01...n87/index.html You probably need to understand what the different "Format" codes are to construct the format portion of the statement. Beyond that, all of the FORTRAN I have seen uses the READ statement rather than the DECODE statement to read input from text file.
Yes, I had the same issue when looking up the DECODE function. I'm converting old Fortran code into something more modern...eventually into some version of C. I'm using vba just to verify I have an understanding of the code. The Fortran output for REAL numbers is G14.7 so I think I can get by with "0.######". Adding another hashtag causes problems again. I'm exporting to a text file from within vba (so user only has to push one button to import old data and the converted output is saved to a file with different extension). I think I can handle that with the PRINTLINE function, but I have Excel 2016 and the editor help leads to an unfriendly MSDN link.
Anyway, thanks to both of you. I'll forge on.
What result do you want for that number?
I would want 0.500003. Taking "0.######" as the format would give me the desired result. This just happens to be a made up example and I don't know that I'd ever come across a number like this, but I might and so I need to be able to handle it. The ultimate output will have 7 digits after the decimal place. I couldn't get ROUND functions to work but I think I can with the format rule you gave me.
If you are going to 7 digits, 9.899999619 becomes 9.8999996 rather than the 9.9 in the OP.
Yes that's true. I'm thinking I'll go with 6 #s to avoid the issue. There's a balance in there somewhere. Ultimately I need to determine where the cutoff is...
The 9.9 is the exact result, the 9.899999619 is an artifact of decoding the binary representation. The difference may not affect ultimate calculations that this
is being fed to (but it may)...but for readability purposes I would like to show the 9.9 in the resulting text file.
Still...the point you make is very valid. Using 6 instead of 7 #s is somewhat of a cheat. I'll have to think on this more. Maybe formatting the F value before calculating
the final number is the way to go.
It might depend on exactly what you mean by 7 digits. I meant 7 sig fig, 9.899999(619)E0, which is different from 7 digits past the decimal point 9.8999996(19)E0. Your original examples showed numbers of varying scales (some less than 1 some greater than 1), which is part of why I suggested scientific notation for these. It is up to you to know exactly what you will need.
Perhaps as an example, assuming all of the exponent (e) digits can be 0, then the exponent portion could yield E=0*2^0-127 or -127. The mantissa (f) looks like it will always be between 1 and 10, so 10*2^-127 which is about 5.877472E-38 to 7 sig figs, but will be exactly 0 to 7 digits past the decimal point. Part of thinking through this is to consider exactly what range of values you can actually get from this operation.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks