Nov 23 2020 05:39 AM
Nov 23 2020 05:39 AM
I have a 7-character field, which must receive a number. The length of the field is fixed: 7 characters. For instance, the field is located from column 10 to 16 (inclusive) as in the following example (imagine an Hollerith card!!!) where the field is shown with equal signs (i.e. =).
The first character must be a '-' (i.e. minus sign) for a negative number, or a space for a positive number. the number can be any real number, positive or negative. The number must be rounded correctly.
Let's take an example: 347.2952. It should result in
Note that the number has been rounded correctly. If the number was negative, it would be
If the number was 1., the result would be
Note the result does not need to be padded with zero to the right.
Here is another example: 0.3472952. The result would be
which would be even better since it carry potentially more precision.
I am seeking the simplest solution with a combination of TEXT, LEFT, RIGHT, CONCATENATE, IF, etc. functions.
Thank you for your help.
Nov 23 2020 06:35 AMSolution
Lets say you have a number such as 347.2952 in cell A1.
Enter the following formula in B1:
This can be filled down.
Warning: the number in A1 must have at most 5 digits before the decimal point.
12345.678 will work, but 123456.78 won't.
Nov 23 2020 11:22 PM
@Hans Vogelaar Thank you Hans for your quick response. Yes, it works. To be honest, I am a bit disappointed. Actually, I came up to the same conclusion: logs would resolve the issue. It is hard to believe that there is no simpler solution for fixed length field, which was the norm some (long) times ago.
Actually, I mixed two things in my question. I wanted positive and negative numbers to be rounded the same way. This particular request can be resolved by testing the sign. (In my application, the sum of all numbers must be equal to zero without any residual.) The second part of the question is related to the fixed length field and here again for me the use of LEFT or RIGHT, TEXT and CONCATENATE is required, I think. It is too bad that Excel does not have a simpler solution. Of course, there is always VBA.
If you have some second thought, it would be appreciated. In any case, thank you very much for your working solution.
PS: 60 years ago, my neighbor's name was Vogelaar way back then when I lived in Uccle, Belgium.