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?

"PWS" wrote...
....
=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.

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")

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.

"PWS" wrote:

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?

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

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

On 8 Mar 2007 23:58:02 -0800, "PWS" wrote:

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?

Try this:

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

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

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

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")