strange stuff using Fixed function

A

Alex

in Sheet2, in cell F1, I have the following formula:

="Number is " & sheet1!A1 & "in total"

In sheet1, the Cell A1 is a calculated number with about
12 decimal points.

The problem is that what I get in cell F1 of sheet2, is
the following: Number of Patients is 11898.8648158168 in
total.

I want this not to show any decimals. so I tried using
FIXED function in the formula such as: ="Number is "&Fixed
(sheet1!A1,0)&"in Total". This gives a strange result,
it now puts commas but also moves the decimal to 5 place
to the right, like so: 11,899,27082.1570981921 makes no
sense to me.

I even tried using Text function in the formula and that
doesn't make any changes at all.

Any help please?

Thanks folks

Alex
 
T

Trevor Shuttleworth

Alex

one way:

="Number is " & ROUND(Sheet1!A1,0) & " in total"

Regards

Trevor
 
A

Alex

Hi Trevor,

I just tried Round function and it gives me the same
weired 11899,27082.157098...
Why is it doing this ?

Any idea?
 
D

Dave Peterson

You sure you're looking at A1 in sheet1?


Hi Trevor,

I just tried Round function and it gives me the same
weired 11899,27082.157098...
Why is it doing this ?

Any idea?
Click to expand...
 
J

JE McGimpsey

one way:

To round to nearest integer:

="Number is " & TEXT(Sheet1!A1,"0") & " in total"


or, to truncate decimal portion:

="Number is " & TRUNC(Sheet1!A1) & " in total"
 
A

Alex

Hi, yea, I've tried everything that has been suggested.
But I still get the same strange result.
 
A

Alex

Hi Thanks for helping.
I've tried both Text (sheet1!A1,"0") and even Trunc, but I
get the same problem. Its as though nothing happens !!
 
B

Biff

Hi Alex!

Don't know why those things are happening but you could
try this:

="Number is "&INT(sheet1!A1)&" in Total"

INT() returns the integer of a decimal number:

11898.8648158168 = 11898

Biff
 
E

Earl Kiosterud

Alex,

For the formula in Sheet 2, F1:
=Sheet1!A1

For A1, Format - Cells - Number tab - Custom

"Number is" ###,### " in total"
"Number is" # " in total"

These will round to nearest whole integer, not drop the fractional part.
 
D

Dave Peterson

Try rebuilding the formula:

type:
="Number is " & ROUND(
but then use your mouse to point at the cell you want (sheet1!a1), then continue
with the typing:
,0) & " in total"


Do you get the same formula?
 
M

Max

Alex said:
In sheet1, the Cell A1 is a calculated number
with about 12 decimal points ..
Click to expand...
.. in Sheet2, in cell F1, I have the following formula:
="Number is " & sheet1!A1 & "in total"
I want this not to show any decimals ..
Click to expand...
... I even tried using Text function in the formula and that
doesn't make any changes at all..
Click to expand...

In Sheet2,

Try in F1: ="Number is " & TEXT(Sheet1!A1,"0") & " in total"

This seems to work for me
 
Top