Rounding Problem

P

PWS

I have a requirement to displace the follwoing pair of data items as
shown:

A simple example is:

2.753, 2.848 ....... 2.75 / 85

I have the following formula:

=TEXT(2.753;"0.00")&" / "&TEXT((2.848-INT(2.848))*100;"00")

Obviously on the workbook the values as cell references

Using the follwoing example I get a 'wrong' result.

2.850 - 2.995 should be shown as 2.85 / 00, but is shown as 2.85 /
100.

I do not understand how 995 becomes 100 rtaher than 00, when the
format requested is "00", but it does.

Can anyone think of a way to achieve what I am looking for?
 
H

Harlan Grove

PWS said:
=TEXT(2.753;"0.00")&" / "&TEXT((2.848-INT(2.848))*100;"00")

Obviously on the workbook the values as cell references

Using the follwoing example I get a 'wrong' result.

2.850 - 2.995 should be shown as 2.85 / 00, but is shown as
2.85 / 100.

I do not understand how 995 becomes 100 rtaher than 00, when the
format requested is "00", but it does.
Click to expand...

Well, it's not 995, it's 99.5, and it rounds up to 100, and 100 in
"00" format displays as 100. If you want to it to round to be 00, you
need to toss in ROUND and MOD calls.

=TEXT(A2,"0.00 \/ ")&TEXT(MOD(ROUND((B2-INT(B2))*100,0),1),"00")
 
J

Joel

I got 2.75 / 85. It is round numbers when using the test() function.
Highlight the cell and then in the Tools Menu - Formula Auditing - Evaluate
Formula. Press the Evalute button and see how the formula is being executed.
You will see the problems.
 
P

PWS

=TEXT(A2,"0.00 \/ ")&TEXT(MOD(ROUND((B2-INT(B2))*100,0),1),"00")
Click to expand...

Unless I am implementing something wrongly, your formula works for the
problem example but does not produce the required result for the other
example!
 
R

Ron Rosenfeld

I have a requirement to displace the follwoing pair of data items as
shown:

A simple example is:

2.753, 2.848 ....... 2.75 / 85

I have the following formula:

=TEXT(2.753;"0.00")&" / "&TEXT((2.848-INT(2.848))*100;"00")

Obviously on the workbook the values as cell references

Using the follwoing example I get a 'wrong' result.

2.850 - 2.995 should be shown as 2.85 / 00, but is shown as 2.85 /
100.

I do not understand how 995 becomes 100 rtaher than 00, when the
format requested is "00", but it does.

Can anyone think of a way to achieve what I am looking for?
Click to expand...

Try this:

=TEXT(A1,"0.00") &" / "&RIGHT(TEXT(100*(A2-INT(A2)),"00"),2)
--ron
 
H

Harlan Grove

PWS said:
Unless I am implementing something wrongly, your formula works for the
problem example but does not produce the required result for the other
example!
Click to expand...

I screwed up. The second argument to MOD should be 100, not 1.

=TEXT(A2,"0.00 \/ ")&TEXT(MOD(ROUND((B2-INT(B2))*100,0),100),"00")
 
Top