I need to show only the whole number, not the number resulting by removing
the decimals. We do not want to force all of these cells.

So, if the number is $104,978.6849 it needs to show $104,978, not $104,979

How do I do this?

The formatting toolbar has a button that allows you to decrease the number of
decimals. It looks like an arrow pointing to the right with a bunch of
zeroes on it...

Dave
--
Brevity is the soul of wit.


"kell~0" wrote:

I need to show only the whole number, not the number resulting by removing
the decimals. We do not want to force all of these cells.

So, if the number is $104,978.6849 it needs to show $104,978, not $104,979

How do I do this?

When I use that button, the whole number is rounded up. So, it goes like this:
$104,978.6849
$104,978.685
$104,978.69
$104,978.7
$104,979

It needs to show $104,978.

Any other trick in your bag?

"Dave F" wrote:

The formatting toolbar has a button that allows you to decrease the number of
decimals. It looks like an arrow pointing to the right with a bunch of
zeroes on it...

Dave
--
Brevity is the soul of wit.


"kell~0" wrote:

I need to show only the whole number, not the number resulting by removing
the decimals. We do not want to force all of these cells.

So, if the number is $104,978.6849 it needs to show $104,978, not $104,979

How do I do this?

Odd. It doesn't round up on my machine. What version of XL are you using?
--
Brevity is the soul of wit.


"kell~0" wrote:

When I use that button, the whole number is rounded up. So, it goes like this:
$104,978.6849
$104,978.685
$104,978.69
$104,978.7
$104,979

It needs to show $104,978.

Any other trick in your bag?

"Dave F" wrote:

The formatting toolbar has a button that allows you to decrease the number of
decimals. It looks like an arrow pointing to the right with a bunch of
zeroes on it...

Dave
--
Brevity is the soul of wit.


"kell~0" wrote:

I need to show only the whole number, not the number resulting by removing
the decimals. We do not want to force all of these cells.

So, if the number is $104,978.6849 it needs to show $104,978, not $104,979

How do I do this?

One way
number is in A1

enter in B1
=INT(A1)

Greetings from New Zealand


"Dave F" wrote in message
...
The formatting toolbar has a button that allows you to decrease the number
of
decimals. It looks like an arrow pointing to the right with a bunch of
zeroes on it...

Dave
--
Brevity is the soul of wit.


"kell~0" wrote:

I need to show only the whole number, not the number resulting by
removing
the decimals. We do not want to force all of these cells.

So, if the number is $104,978.6849 it needs to show $104,978, not
$104,979

How do I do this?

=INT(A1)


"kell~0" wrote:

I need to show only the whole number, not the number resulting by removing
the decimals. We do not want to force all of these cells.

So, if the number is $104,978.6849 it needs to show $104,978, not $104,979

How do I do this?

Wouldn't that give $104,979, Dave?

I don't know what the OP meant by "We do not want to force all of these
cells", but I think he needs =INT(A1).
--
David Biddulph

"Dave F" wrote in message
...
The formatting toolbar has a button that allows you to decrease the number
of
decimals. It looks like an arrow pointing to the right with a bunch of
zeroes on it...

Dave

"kell~0" wrote:

I need to show only the whole number, not the number resulting by
removing
the decimals. We do not want to force all of these cells.

So, if the number is $104,978.6849 it needs to show $104,978, not
$104,979

How do I do this?

Well, it doesn't round up on my machine. Not sure why.
--
Brevity is the soul of wit.


"David Biddulph" wrote:

Wouldn't that give $104,979, Dave?

I don't know what the OP meant by "We do not want to force all of these
cells", but I think he needs =INT(A1).
--
David Biddulph

"Dave F" wrote in message
...
The formatting toolbar has a button that allows you to decrease the number
of
decimals. It looks like an arrow pointing to the right with a bunch of
zeroes on it...

Dave

"kell~0" wrote:

I need to show only the whole number, not the number resulting by
removing
the decimals. We do not want to force all of these cells.

So, if the number is $104,978.6849 it needs to show $104,978, not
$104,979

How do I do this?

I don't know where to find the version. I'd suppose that it is probably the
first XP office version ever!

"Dave F" wrote:

Odd. It doesn't round up on my machine. What version of XL are you using?
--
Brevity is the soul of wit.


"kell~0" wrote:

When I use that button, the whole number is rounded up. So, it goes like this:
$104,978.6849
$104,978.685
$104,978.69
$104,978.7
$104,979

It needs to show $104,978.

Any other trick in your bag?

"Dave F" wrote:

The formatting toolbar has a button that allows you to decrease the number of
decimals. It looks like an arrow pointing to the right with a bunch of
zeroes on it...

Dave
--
Brevity is the soul of wit.


"kell~0" wrote:

I need to show only the whole number, not the number resulting by removing
the decimals. We do not want to force all of these cells.

So, if the number is $104,978.6849 it needs to show $104,978, not $104,979

How do I do this?

It works, but is there a way to do this in the same cell?

"Bill Kuunders" wrote:

One way
number is in A1

enter in B1
=INT(A1)

Greetings from New Zealand


"Dave F" wrote in message
...
The formatting toolbar has a button that allows you to decrease the number
of
decimals. It looks like an arrow pointing to the right with a bunch of
zeroes on it...

Dave
--
Brevity is the soul of wit.


"kell~0" wrote:

I need to show only the whole number, not the number resulting by
removing
the decimals. We do not want to force all of these cells.

So, if the number is $104,978.6849 it needs to show $104,978, not
$104,979

How do I do this?

"Force all these cells" I don't want to go back and type in the interger,
because I'd loose the formula.

Kell~0

"Dave F" wrote:

Well, it doesn't round up on my machine. Not sure why.
--
Brevity is the soul of wit.


"David Biddulph" wrote:

Wouldn't that give $104,979, Dave?

I don't know what the OP meant by "We do not want to force all of these
cells", but I think he needs =INT(A1).
--
David Biddulph

"Dave F" wrote in message
...
The formatting toolbar has a button that allows you to decrease the number
of
decimals. It looks like an arrow pointing to the right with a bunch of
zeroes on it...

Dave

"kell~0" wrote:

I need to show only the whole number, not the number resulting by
removing
the decimals. We do not want to force all of these cells.

So, if the number is $104,978.6849 it needs to show $104,978, not
$104,979

How do I do this?

Well is should round. Are you sure you have $104,978.6849 ?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dave F" wrote in message
...
Well, it doesn't round up on my machine. Not sure why.
--
Brevity is the soul of wit.


"David Biddulph" wrote:

Wouldn't that give $104,979, Dave?

I don't know what the OP meant by "We do not want to force all of these
cells", but I think he needs =INT(A1).
--
David Biddulph

"Dave F" wrote in message
...
The formatting toolbar has a button that allows you to decrease the
number
of
decimals. It looks like an arrow pointing to the right with a bunch of
zeroes on it...

Dave

"kell~0" wrote:

I need to show only the whole number, not the number resulting by
removing
the decimals. We do not want to force all of these cells.

So, if the number is $104,978.6849 it needs to show $104,978, not
$104,979

How do I do this?

Isn't it getting dark in New Zealand?

"kell~0" wrote:

It works, but is there a way to do this in the same cell?

"Bill Kuunders" wrote:

One way
number is in A1

enter in B1
=INT(A1)

Greetings from New Zealand


"Dave F" wrote in message
...
The formatting toolbar has a button that allows you to decrease the number
of
decimals. It looks like an arrow pointing to the right with a bunch of
zeroes on it...

Dave
--
Brevity is the soul of wit.


"kell~0" wrote:

I need to show only the whole number, not the number resulting by
removing
the decimals. We do not want to force all of these cells.

So, if the number is $104,978.6849 it needs to show $104,978, not
$104,979

How do I do this?

try one other way,

you can generically show the number of decimals in a key stroke

=trunc("yourCells","yourDigitizerCell")

if you have a column of values, see help files

regards,
dribler
--
*****
birds of the same feather flock together..



"kell~0" wrote:

"Force all these cells" I don't want to go back and type in the interger,
because I'd loose the formula.

Kell~0

"Dave F" wrote:

Well, it doesn't round up on my machine. Not sure why.
--
Brevity is the soul of wit.


"David Biddulph" wrote:

Wouldn't that give $104,979, Dave?

I don't know what the OP meant by "We do not want to force all of these
cells", but I think he needs =INT(A1).
--
David Biddulph

"Dave F" wrote in message
...
The formatting toolbar has a button that allows you to decrease the number
of
decimals. It looks like an arrow pointing to the right with a bunch of
zeroes on it...

Dave

"kell~0" wrote:

I need to show only the whole number, not the number resulting by
removing
the decimals. We do not want to force all of these cells.

So, if the number is $104,978.6849 it needs to show $104,978, not
$104,979

How do I do this?

So ... now we know that you have a formula in the cell!

Simply wrap your formula with either Int() OR Trunc().

=INT(your formula)
=INT(SUM(A1:A100))

=TRUNC(your formula)
=TRUNC(A1*B1)


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"kell~0" wrote in message
...
"Force all these cells" I don't want to go back and type in the interger,
because I'd loose the formula.

Kell~0

"Dave F" wrote:

Well, it doesn't round up on my machine. Not sure why.
--
Brevity is the soul of wit.


"David Biddulph" wrote:

Wouldn't that give $104,979, Dave?

I don't know what the OP meant by "We do not want to force all of these
cells", but I think he needs =INT(A1).
--
David Biddulph

"Dave F" wrote in message
...
The formatting toolbar has a button that allows you to decrease the
number
of
decimals. It looks like an arrow pointing to the right with a bunch
of
zeroes on it...

Dave

"kell~0" wrote:

I need to show only the whole number, not the number resulting by
removing
the decimals. We do not want to force all of these cells.

So, if the number is $104,978.6849 it needs to show $104,978, not
$104,979

How do I do this?

Yes, I also got 104,979 i.e. it was rounded.

However, I discovered something unexpected using that button. In mathematics, I am used to .5 being rounded up. With the button, it is inconsistent - sometimes it does and sometimes it doesn't. Am I missing something? I use V. 2003.

Scenario 1

1234.4567--1234.457--1234.46--1234.5--1234

..5 wasn't rounded up??

Scenario 2

1234.5000 (format = 4 decimal places) then use the button.

Result = 1235

I don't understand this inconsistency and I don't feel comfortable using the button. Well, another anomaly of Excel?

Appreciate any clarification or guidance.

Epinn

"Bernard Liengme" wrote in message ...
Well is should round. Are you sure you have $104,978.6849 ?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dave F" wrote in message
...
Well, it doesn't round up on my machine. Not sure why.
--
Brevity is the soul of wit.


"David Biddulph" wrote:

Wouldn't that give $104,979, Dave?

I don't know what the OP meant by "We do not want to force all of these
cells", but I think he needs =INT(A1).
--
David Biddulph

"Dave F" wrote in message
...
The formatting toolbar has a button that allows you to decrease the
number
of
decimals. It looks like an arrow pointing to the right with a bunch of
zeroes on it...

Dave

"kell~0" wrote:

I need to show only the whole number, not the number resulting by
removing
the decimals. We do not want to force all of these cells.

So, if the number is $104,978.6849 it needs to show $104,978, not
$104,979

How do I do this?

Epinn,

The reason that it didn't round up in scenario 1 was that you are not
rounding from .5, but from .4567. Remember that the cell still contains the
full number, and the rounding (by cell format as controlled by the button)
only affects what is displayed. [You can go back to more decimal places by
the other button with the reverse function.]

If you want to change the value in the cell, use the ROUND() function.
--
David Biddulph

"Epinn" wrote in message
...
Yes, I also got 104,979 i.e. it was rounded.

However, I discovered something unexpected using that button. In
mathematics, I am used to .5 being rounded up. With the button, it is
inconsistent - sometimes it does and sometimes it doesn't. Am I missing
something? I use V. 2003.

Scenario 1

1234.4567--1234.457--1234.46--1234.5--1234

..5 wasn't rounded up??

Scenario 2

1234.5000 (format = 4 decimal places) then use the button.

Result = 1235

I don't understand this inconsistency and I don't feel comfortable using the
button. Well, another anomaly of Excel?

Appreciate any clarification or guidance.

Epinn

"Bernard Liengme" wrote in message
...
Well is should round. Are you sure you have $104,978.6849 ?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dave F" wrote in message
...
Well, it doesn't round up on my machine. Not sure why.
--
Brevity is the soul of wit.


"David Biddulph" wrote:

Wouldn't that give $104,979, Dave?

I don't know what the OP meant by "We do not want to force all of these
cells", but I think he needs =INT(A1).
--
David Biddulph

"Dave F" wrote in message
...
The formatting toolbar has a button that allows you to decrease the
number
of
decimals. It looks like an arrow pointing to the right with a bunch of
zeroes on it...

Dave

"kell~0" wrote:

I need to show only the whole number, not the number resulting by
removing
the decimals. We do not want to force all of these cells.

So, if the number is $104,978.6849 it needs to show $104,978, not
$104,979

How do I do this?

Thanks David. I was aware of what you said except "only affects what is displayed." I was surprised because it rounded up the digits to the *right* of the decimal and so I expected the same to the *left* of the decimal.

I was watching the following unfold and I saw the second last one was 1234.5.

1234.4567--1234.457--1234.46--1234.5--1234

I'll accept the fact that the button behaves differently from ROUND( ). Thanks again for highlighting the nature of the button.

Epinn

"David Biddulph" wrote in message ...
Epinn,

The reason that it didn't round up in scenario 1 was that you are not
rounding from .5, but from .4567. Remember that the cell still contains the
full number, and the rounding (by cell format as controlled by the button)
only affects what is displayed. [You can go back to more decimal places by
the other button with the reverse function.]

If you want to change the value in the cell, use the ROUND() function.
--
David Biddulph

"Epinn" wrote in message
...
Yes, I also got 104,979 i.e. it was rounded.

However, I discovered something unexpected using that button. In
mathematics, I am used to .5 being rounded up. With the button, it is
inconsistent - sometimes it does and sometimes it doesn't. Am I missing
something? I use V. 2003.

Scenario 1

1234.4567--1234.457--1234.46--1234.5--1234

..5 wasn't rounded up??

Scenario 2

1234.5000 (format = 4 decimal places) then use the button.

Result = 1235

I don't understand this inconsistency and I don't feel comfortable using the
button. Well, another anomaly of Excel?

Appreciate any clarification or guidance.

Epinn

"Bernard Liengme" wrote in message
...
Well is should round. Are you sure you have $104,978.6849 ?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dave F" wrote in message
...
Well, it doesn't round up on my machine. Not sure why.
--
Brevity is the soul of wit.


"David Biddulph" wrote:

Wouldn't that give $104,979, Dave?

I don't know what the OP meant by "We do not want to force all of these
cells", but I think he needs =INT(A1).
--
David Biddulph

"Dave F" wrote in message
...
The formatting toolbar has a button that allows you to decrease the
number
of
decimals. It looks like an arrow pointing to the right with a bunch of
zeroes on it...

Dave

"kell~0" wrote:

I need to show only the whole number, not the number resulting by
removing
the decimals. We do not want to force all of these cells.

So, if the number is $104,978.6849 it needs to show $104,978, not
$104,979

How do I do this?

I think you'll find that the button has exactly the same effect as changing
the number of decimal places in Format/ Cells/ Number.

Note also that it won't behave any differently before the decimal point from
after. If you have 1234.4448 it would change to 1234.445 but then to
1234.44.

The reason you saw it rounding up the numbers after the decimal point is
that each digit was greater than 5, until you got to 1234.5
--
David Biddulph

"Epinn" wrote in message
...
Thanks David. I was aware of what you said except "only affects what is
displayed." I was surprised because it rounded up the digits to the *right*
of the decimal and so I expected the same to the *left* of the decimal.

I was watching the following unfold and I saw the second last one was
1234.5.

1234.4567--1234.457--1234.46--1234.5--1234

I'll accept the fact that the button behaves differently from ROUND( ).
Thanks again for highlighting the nature of the button.

Epinn

"David Biddulph" wrote in message
...
Epinn,

The reason that it didn't round up in scenario 1 was that you are not
rounding from .5, but from .4567. Remember that the cell still contains the
full number, and the rounding (by cell format as controlled by the button)
only affects what is displayed. [You can go back to more decimal places by
the other button with the reverse function.]

If you want to change the value in the cell, use the ROUND() function.
--
David Biddulph

"Epinn" wrote in message
...
Yes, I also got 104,979 i.e. it was rounded.

However, I discovered something unexpected using that button. In
mathematics, I am used to .5 being rounded up. With the button, it is
inconsistent - sometimes it does and sometimes it doesn't. Am I missing
something? I use V. 2003.

Scenario 1

1234.4567--1234.457--1234.46--1234.5--1234

..5 wasn't rounded up??

Scenario 2

1234.5000 (format = 4 decimal places) then use the button.

Result = 1235

I don't understand this inconsistency and I don't feel comfortable using the
button. Well, another anomaly of Excel?

Appreciate any clarification or guidance.

Epinn

"Bernard Liengme" wrote in message
...
Well is should round. Are you sure you have $104,978.6849 ?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dave F" wrote in message
...
Well, it doesn't round up on my machine. Not sure why.
--
Brevity is the soul of wit.


"David Biddulph" wrote:

Wouldn't that give $104,979, Dave?

I don't know what the OP meant by "We do not want to force all of these
cells", but I think he needs =INT(A1).
--
David Biddulph

"Dave F" wrote in message
...
The formatting toolbar has a button that allows you to decrease the
number
of
decimals. It looks like an arrow pointing to the right with a bunch of
zeroes on it...

Dave

"kell~0" wrote:

I need to show only the whole number, not the number resulting by
removing
the decimals. We do not want to force all of these cells.

So, if the number is $104,978.6849 it needs to show $104,978, not
$104,979

How do I do this?

The =INT(MyFormula) is working. However, when I enter it it affects the
formula in the next cell and the next cell all the way down the row.

I'm sorry I wasn't clear when I asked yesterday.

"Gord Dibben" wrote:

Good point Peo.

Gord

On Tue, 09 Jan 2007 13:44:41 -0800, Peo Sjoblom wrote:

Actually if the OP needs to do this it is better to use TRUNC since
INT will round down to the nearest integer, that means a negative value
like -123,478.99 will return -123,479 with INT and -123,478 with TRUNC


Regards,

Peo Sjoblom

Gord Dibben wrote:
You could use a helper column and =INT(cellref) which will return only the
number without decimal rounding up.

This will affect the value of the number however and may not be what you desire.

104,978.6849 will become 104,978.0000 which you then format for no DP


Gord Dibben MS Excel MVP

On Tue, 9 Jan 2007 11:44:00 -0800, kell~0
wrote:

When I use that button, the whole number is rounded up. So, it goes like this:
$104,978.6849
$104,978.685
$104,978.69
$104,978.7
$104,979

It needs to show $104,978.

Any other trick in your bag?

"Dave F" wrote:

The formatting toolbar has a button that allows you to decrease the number of
decimals. It looks like an arrow pointing to the right with a bunch of
zeroes on it...

Dave
--
Brevity is the soul of wit.


"kell~0" wrote:

I need to show only the whole number, not the number resulting by removing
the decimals. We do not want to force all of these cells.

So, if the number is $104,978.6849 it needs to show $104,978, not $104,979

How do I do this?

yah, that's right....

but if u wish to retain the real values whenever it is chained in a series
of other formulas, you better make a helper <row,column or cell
one for the purpose of SHOWING
and the other for the sake of other linked formulas....

maybe it's possible
--
*****
birds of the same feather flock together..



"RagDyer" wrote:

So ... now we know that you have a formula in the cell!

Simply wrap your formula with either Int() OR Trunc().

=INT(your formula)
=INT(SUM(A1:A100))

=TRUNC(your formula)
=TRUNC(A1*B1)


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"kell~0" wrote in message
...
"Force all these cells" I don't want to go back and type in the interger,
because I'd loose the formula.

Kell~0

"Dave F" wrote:

Well, it doesn't round up on my machine. Not sure why.
--
Brevity is the soul of wit.


"David Biddulph" wrote:

Wouldn't that give $104,979, Dave?

I don't know what the OP meant by "We do not want to force all of these
cells", but I think he needs =INT(A1).
--
David Biddulph

"Dave F" wrote in message
...
The formatting toolbar has a button that allows you to decrease the
number
of
decimals. It looks like an arrow pointing to the right with a bunch
of
zeroes on it...

Dave

"kell~0" wrote:

I need to show only the whole number, not the number resulting by
removing
the decimals. We do not want to force all of these cells.

So, if the number is $104,978.6849 it needs to show $104,978, not
$104,979

How do I do this?

=int(x)

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com