Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default Trim varying decimals from column of numbers

Good Morning
I am using Excel 2003 and have a column of approximately 250 numbers. Each
has different number of decimals. Although the Format displays all as whole
numbers, the decimals are be calculated and throowing my cost analysis off
when Multplying this column (Quantity) x a Price. How can I trim all values
to whole numbers, regardless of their vary number of decimal places.

Thanks in Advance
Phil
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Trim varying decimals from column of numbers

the decimals are be calculated

If these numbers are the results of formulas then try something like this:

=INT(your_formula_here)

--
Biff
Microsoft Excel MVP


"AuthorizedUserPF" wrote in
message ...
Good Morning
I am using Excel 2003 and have a column of approximately 250 numbers. Each
has different number of decimals. Although the Format displays all as
whole
numbers, the decimals are be calculated and throowing my cost analysis off
when Multplying this column (Quantity) x a Price. How can I trim all
values
to whole numbers, regardless of their vary number of decimal places.

Thanks in Advance
Phil



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 216
Default Trim varying decimals from column of numbers

You could try =DOLLAR(cell,#) where #=number of decimals.

"T. Valko" wrote:

the decimals are be calculated


If these numbers are the results of formulas then try something like this:

=INT(your_formula_here)

--
Biff
Microsoft Excel MVP


"AuthorizedUserPF" wrote in
message ...
Good Morning
I am using Excel 2003 and have a column of approximately 250 numbers. Each
has different number of decimals. Although the Format displays all as
whole
numbers, the decimals are be calculated and throowing my cost analysis off
when Multplying this column (Quantity) x a Price. How can I trim all
values
to whole numbers, regardless of their vary number of decimal places.

Thanks in Advance
Phil



.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 216
Default Trim varying decimals from column of numbers

Forgot...use Ctrl+Enter after typing the formula.

"Nadine" wrote:

You could try =DOLLAR(cell,#) where #=number of decimals.

"T. Valko" wrote:

the decimals are be calculated


If these numbers are the results of formulas then try something like this:

=INT(your_formula_here)

--
Biff
Microsoft Excel MVP


"AuthorizedUserPF" wrote in
message ...
Good Morning
I am using Excel 2003 and have a column of approximately 250 numbers. Each
has different number of decimals. Although the Format displays all as
whole
numbers, the decimals are be calculated and throowing my cost analysis off
when Multplying this column (Quantity) x a Price. How can I trim all
values
to whole numbers, regardless of their vary number of decimal places.

Thanks in Advance
Phil



.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default Trim varying decimals from column of numbers

The problem with this is that the decimals vary. Somne show 3 decimals, while
others shor 4 ot 5 decimals.

"Nadine" wrote:

Forgot...use Ctrl+Enter after typing the formula.

"Nadine" wrote:

You could try =DOLLAR(cell,#) where #=number of decimals.

"T. Valko" wrote:

the decimals are be calculated

If these numbers are the results of formulas then try something like this:

=INT(your_formula_here)

--
Biff
Microsoft Excel MVP


"AuthorizedUserPF" wrote in
message ...
Good Morning
I am using Excel 2003 and have a column of approximately 250 numbers. Each
has different number of decimals. Although the Format displays all as
whole
numbers, the decimals are be calculated and throowing my cost analysis off
when Multplying this column (Quantity) x a Price. How can I trim all
values
to whole numbers, regardless of their vary number of decimal places.

Thanks in Advance
Phil


.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Trim varying decimals from column of numbers

Did you try my suggestion?

--
Biff
Microsoft Excel MVP


"AuthorizedUserPF" wrote in
message ...
The problem with this is that the decimals vary. Somne show 3 decimals,
while
others shor 4 ot 5 decimals.

"Nadine" wrote:

Forgot...use Ctrl+Enter after typing the formula.

"Nadine" wrote:

You could try =DOLLAR(cell,#) where #=number of decimals.

"T. Valko" wrote:

the decimals are be calculated

If these numbers are the results of formulas then try something like
this:

=INT(your_formula_here)

--
Biff
Microsoft Excel MVP


"AuthorizedUserPF" wrote
in
message ...
Good Morning
I am using Excel 2003 and have a column of approximately 250
numbers. Each
has different number of decimals. Although the Format displays all
as
whole
numbers, the decimals are be calculated and throowing my cost
analysis off
when Multplying this column (Quantity) x a Price. How can I trim
all
values
to whole numbers, regardless of their vary number of decimal
places.

Thanks in Advance
Phil


.



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default Trim varying decimals from column of numbers

There is no formulae. The numbers aparently came over from another
spreadsheet as values withb the decimals alreaddy there.

"T. Valko" wrote:

Did you try my suggestion?

--
Biff
Microsoft Excel MVP


"AuthorizedUserPF" wrote in
message ...
The problem with this is that the decimals vary. Somne show 3 decimals,
while
others shor 4 ot 5 decimals.

"Nadine" wrote:

Forgot...use Ctrl+Enter after typing the formula.

"Nadine" wrote:

You could try =DOLLAR(cell,#) where #=number of decimals.

"T. Valko" wrote:

the decimals are be calculated

If these numbers are the results of formulas then try something like
this:

=INT(your_formula_here)

--
Biff
Microsoft Excel MVP


"AuthorizedUserPF" wrote
in
message ...
Good Morning
I am using Excel 2003 and have a column of approximately 250
numbers. Each
has different number of decimals. Although the Format displays all
as
whole
numbers, the decimals are be calculated and throowing my cost
analysis off
when Multplying this column (Quantity) x a Price. How can I trim
all
values
to whole numbers, regardless of their vary number of decimal
places.

Thanks in Advance
Phil


.



.

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Trim varying decimals from column of numbers

Ok, let's assume your numbers are in the range A2:A100. Enter this formula
in B2 and copy down as needed:

=INT(A2)

--
Biff
Microsoft Excel MVP


"AuthorizedUserPF" wrote in
message ...
There is no formulae. The numbers aparently came over from another
spreadsheet as values withb the decimals alreaddy there.

"T. Valko" wrote:

Did you try my suggestion?

--
Biff
Microsoft Excel MVP


"AuthorizedUserPF" wrote in
message ...
The problem with this is that the decimals vary. Somne show 3 decimals,
while
others shor 4 ot 5 decimals.

"Nadine" wrote:

Forgot...use Ctrl+Enter after typing the formula.

"Nadine" wrote:

You could try =DOLLAR(cell,#) where #=number of decimals.

"T. Valko" wrote:

the decimals are be calculated

If these numbers are the results of formulas then try something
like
this:

=INT(your_formula_here)

--
Biff
Microsoft Excel MVP


"AuthorizedUserPF"
wrote
in
message ...
Good Morning
I am using Excel 2003 and have a column of approximately 250
numbers. Each
has different number of decimals. Although the Format displays
all
as
whole
numbers, the decimals are be calculated and throowing my cost
analysis off
when Multplying this column (Quantity) x a Price. How can I trim
all
values
to whole numbers, regardless of their vary number of decimal
places.

Thanks in Advance
Phil


.



.



  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default Trim varying decimals from column of numbers

Thanks that works. Can we add anything to it that would round up at = .5
and down at <= .4

"T. Valko" wrote:

Ok, let's assume your numbers are in the range A2:A100. Enter this formula
in B2 and copy down as needed:

=INT(A2)

--
Biff
Microsoft Excel MVP


"AuthorizedUserPF" wrote in
message ...
There is no formulae. The numbers aparently came over from another
spreadsheet as values withb the decimals alreaddy there.

"T. Valko" wrote:

Did you try my suggestion?

--
Biff
Microsoft Excel MVP


"AuthorizedUserPF" wrote in
message ...
The problem with this is that the decimals vary. Somne show 3 decimals,
while
others shor 4 ot 5 decimals.

"Nadine" wrote:

Forgot...use Ctrl+Enter after typing the formula.

"Nadine" wrote:

You could try =DOLLAR(cell,#) where #=number of decimals.

"T. Valko" wrote:

the decimals are be calculated

If these numbers are the results of formulas then try something
like
this:

=INT(your_formula_here)

--
Biff
Microsoft Excel MVP


"AuthorizedUserPF"
wrote
in
message ...
Good Morning
I am using Excel 2003 and have a column of approximately 250
numbers. Each
has different number of decimals. Although the Format displays
all
as
whole
numbers, the decimals are be calculated and throowing my cost
analysis off
when Multplying this column (Quantity) x a Price. How can I trim
all
values
to whole numbers, regardless of their vary number of decimal
places.

Thanks in Advance
Phil


.



.



.

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Trim varying decimals from column of numbers

Use the round function...

=ROUND(A1,n)

Where n is the number of decimal places to round to.

A1 = 49.537

=ROUND(A1,2) = 49.54
=ROUND(A1,1) = 49.5
=ROUND(A1,0) = 50

--
Biff
Microsoft Excel MVP


"AuthorizedUserPF" wrote in
message ...
Thanks that works. Can we add anything to it that would round up at = .5
and down at <= .4

"T. Valko" wrote:

Ok, let's assume your numbers are in the range A2:A100. Enter this
formula
in B2 and copy down as needed:

=INT(A2)

--
Biff
Microsoft Excel MVP


"AuthorizedUserPF" wrote in
message ...
There is no formulae. The numbers aparently came over from another
spreadsheet as values withb the decimals alreaddy there.

"T. Valko" wrote:

Did you try my suggestion?

--
Biff
Microsoft Excel MVP


"AuthorizedUserPF" wrote
in
message ...
The problem with this is that the decimals vary. Somne show 3
decimals,
while
others shor 4 ot 5 decimals.

"Nadine" wrote:

Forgot...use Ctrl+Enter after typing the formula.

"Nadine" wrote:

You could try =DOLLAR(cell,#) where #=number of decimals.

"T. Valko" wrote:

the decimals are be calculated

If these numbers are the results of formulas then try something
like
this:

=INT(your_formula_here)

--
Biff
Microsoft Excel MVP


"AuthorizedUserPF"
wrote
in
message
...
Good Morning
I am using Excel 2003 and have a column of approximately 250
numbers. Each
has different number of decimals. Although the Format
displays
all
as
whole
numbers, the decimals are be calculated and throowing my cost
analysis off
when Multplying this column (Quantity) x a Price. How can I
trim
all
values
to whole numbers, regardless of their vary number of decimal
places.

Thanks in Advance
Phil


.



.



.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto fill column with sequential numbers with decimals Dogstar Excel Discussion (Misc queries) 2 March 12th 10 11:52 AM
Trim Numbers Deepak Excel Discussion (Misc queries) 3 December 25th 08 02:14 AM
How do I retain the format of varying numbers in Excel? xardoz Excel Discussion (Misc queries) 6 June 20th 06 05:16 PM
linking one column or varying range to another column using same f areezm Excel Worksheet Functions 0 June 6th 06 04:37 PM
Varying column width in a column chart Silvie Dedkova Charts and Charting in Excel 1 March 22nd 05 02:53 PM


All times are GMT +1. The time now is 10:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"