help with 32nd rounding

Anyone have an answer?

I don't understand what you are trying to do. Do you want the user to enter a value that is then converted into this format? Or do you want to change a list of decimal numbers to this format? Or change from this format to binary? Or what?

Sorry for not being clear.

I want the user to be able to enter a value and then it's converted into this format. I also need the number in front of the ' to round up 1 when the number behind the ' goes over 32.

Almost there!

The number in column "B" is how I need the number to appear, however the rounding is off.

Once the number behind the ( ' )goes over 32 I need the number to the left of the ( ') to increase by 1.

For example, using the sam numbers you used:

--A--- ---B----
1 953.78 955'04.0
2 651.28 651'28.0
3 123.59 124'27.0
4 347.24 347'24.0
5 6.71 8'07.0

In the formula you supplied I notice the 64, is that how this formula rounds? I need 32nd rounding after the (').

Originally Posted by shg

You can't do it with formatting, but you can use a formula that displays a number entered in another cell in that fashion:

=INT(A1) & "'" & TEXT( ROUND( (A1 - INT(A1)) * 64, 0) / 2, "00.0")

For example,

Please Login or Register  to view this content.

Try

=INT(A1)+INT(MOD(A1,1)/0.32)&TEXT(MOD(MOD(A1,1)*100,32),"'00.0")

one last question,
is it possible to add the formula to a cell that already has a value in it?
I have 113255 in cell B3, I need this formula to turn that number in B3 to 113'25.5

To do that you'd need to use VBA, I believe. Not really my field........

BTW if B3 rounds to 113'25.5 then are you rounding in that case to the 1/64?

For whatever reason, I assumed the digit past the decimal should always be 0 or 5 ...

=INT(A1) & "'" & TEXT( (A1 - INT(A1)) * 32, 0), "00.0")

Please Login or Register  to view this content.

You could format the cell as ###"'"00"."0 and it would appear as you wish, but computationally it would still be the original number.

Oh okay,
thanks for all the help you two have given me!