Excel Sorting

[waggythegeek]

I have a large list of numbers in excel, most of which are decimals(don't ask). If I want to see just the whole numbers, is there a fast, efficient way to?

[Mightyinvader]

http://www.excelforum.com/excel-general/735722-excel-converting-decimals-to-whole-numbers.html

http://www.teachexcel.com/excel-help/excel-how-to.php?i=51243

some useful threads of the mighty hype engine

[Toddskins]

Two ways come to mind immediately.  Are you wishing to convert all your numbers with decimals into whole numbers, or are you just wanting to search for those that are whole numbers?

 

Example 1:  Take the Integer of the highlight number.

 

     A               B        (Result)

   5.5      =INT(A1)         5            Create formula in Column B, 

   4.9      =INT(A2)         4             then copy formula down the column to create the other formula cells, which I typed in just now.           

   4.2      =INT(A3)         4

 

This example chops off everything to the right of the decimal leaving only the whole number.

 

- - - - - - - - - - - - - -
Example 2:

 

     A          (result of formatting using 0 decimal places will cause the numbers to be rounded up and down accordingly).

   5.5       6

   4.9       5

   4.2       4

 

 

Highlight the column of numbers (column A), then right-click the highlighted area, select "Format Cells", choose "Number" from the Category type, and then change the number of decimal places to zero (0), and it will then round up or down and turn your numbers into whole numbers (but the decimal parts are still remembered if you need to do calculation on them.

[waggythegeek]

Two ways come to mind immediately. Are you wishing to convert all your numbers with decimals into whole numbers, or are you just wanting to search for those that are whole numbers?

Example 1: Take the Integer of the highlight number.

A B (Result)

5.5 =INT(A1) 5 Create formula in Column B,

4.9 =INT(A2) 4 then copy formula down the column to create the other formula cells, which I typed in just now.

4.2 =INT(A3) 4

This example chops off everything to the right of the decimal leaving only the whole number.

- - - - - - - - - - - - - -

Example 2:

A (result of formatting using 0 decimal places will cause the numbers to be rounded up and down accordingly).

5.5 6

4.9 5

4.2 4

Highlight the column of numbers (column A), then right-click the highlighted area, select "Format Cells", choose "Number" from the Category type, and then change the number of decimal places to zero (0), and it will then round up or down and turn your numbers into whole numbers (but the decimal parts are still remembered if you need to do calculation on them.

Just wanna search for whole numbers. Those without whole numbers aren't important to me.

[marioo1182]

I have a large list of numbers in excel, most of which are decimals(don't ask). If I want to see just the whole numbers, is there a fast, efficient way to?

Under the Home Tab, and then under the Number sub tab use the left and right move decimal button to increase or decrease the amount of shown numbers.

In addition in the number tab, you can change from General to Scientific notation, to percent, or whatever you would like. Often I use engineering notation.

Hope this helps!

Don't forget to put Answered!

[Toddskins]

Just wanna search for whole numbers. Those without whole numbers aren't important to me.

 

Okay, try this formula in the cell next to the cell with the number.

 

=INT(A1)=A1

This will evaluate the number and if it is an Integer (whole number), it will report True.  If not, then False.  You can customize the result further, but this should get you going in the right direction.  For example, after you get your results of True and False, you could then count all those cells that are true.

 

 

NOTE: empty cells will get reported as True.  You could further evaluate the number for Length, if you wish to avoid the empty cell as being reported as true.

[waggythegeek]

Okay, try this formula in the cell next to the cell with the number.

=INT(A1)=A1

This will evaluate the number and if it is an Integer (whole number), it will report True. If not, then False. You can customize the result further, but this should get you going in the right direction. For example, after you get your results of True and False, you could then count all those cells that are true.

NOTE: empty cells will get reported as True. You could further evaluate the number for Length, if you wish to avoid the empty cell as being reported as true.

Clever! Never would have thought of this. Check to see if rounded= not rounded. Exactly what I was looking for. Thanks so much!