Free Microsoft Excel 2013 Quick Reference

Displaying only digits after the decimal point

If i have a '13.75' in a cell, I would like to display just the .75 in
another cell. How do I do this?


I have a spreadsheet which contains numbers with lots of trailing digits after the decimal point, eg 15.87492105 and 0.618181818. How can I reduce the number of trailing digits, for example so that the first number would become 15.87 and the second would become 0.62?

Going to Format -> Cells and changing the number of decimal places doesn't work as it doesn't change the actual numbers, it only changes how the numbers are displayed within the cells on the screen. What I need is a method that changes the underlying number. Does anyone have any ideas how this can be achieved? Presumably it would be some kind of VBA procedure which would loop through all the affected cells.

I want to be able to display the number that is so many spaces to the left
and right of the decimal point. Example: 1234.56 How do I put the
second digit to the left of the decimal point (number 3) in a different cell
in Excel? How do I put the first digit to the right of the decimal point
(number 5) in a different cell in Excel? What function would I use?

Hi,
I want a vba code that will remove everything after the decimal pooint in the entire workbook,without rounding the numbers.
then remove all the formulas throughout the entire workbook that is present there.
thanks for help.

Hello,

Here is my concern:
I wish to automatize the display of digit after the coma based on the value.
For instance if the result of a calculation made in a cell is: 9.8, I wish Excel to display 9.8 but if the value becomes higher than 100, I wish Excel to remove the digit after the coma. For instance if the result of a calculation is: 102.8, I wish Excel to round it and display 103.
Or it would be even more flexible if I could set a value, for instance 90 so that when the result of my calculation is above 90, it does not display the digit anymore.

Thank you for your help. This is a big issue for me.

Phil

Hi! Long time lurker, first time poster!

In Excel 2010, is there a way to limit the number of figures after a decimal point that someone can enter into a cell? I'm already using Data Validation to allow decimals equal to or greater than 0, but I'd ideally like to stop someone entering more than 2 digits after the decimal point, but I don't seem to be able to find a way to do this. Is there anything I'm missing?

Thanks

Hello,
I want to display only the number from the left of the decimal point from a scientific notation number. Example:

Cell A1:
1.834E10 Cell B1: 1 Answer

7.123E-12 Cell B1: 7 Answer

3.792E38 Cell B1: 3 Answer

Which ever scientific notation I enter in Cell A1, The formula in Cell B1 will only display the left most digit from the decimal point.

I tried using the Left function and it work, however when I want to use that number in Cell B1 into another formula it did not work because that number is consider as a text not a number. So the Left function is not the one to use.

So how do I write that formula in Cell B1 to display the left most digit from the decimal point from a scientific notation number in Cell A1 and still be a number and not a text?

Sincerely,
Arthur

How to read the display instead of underlying number?

For example, the underlying number is 8.733. I formated the cell with only one digit after the decimal point. Number 8.7 is displayed now. But if I read it by

xlsheet.cells(row, col). I got number 8.733. I want value 8.7 though.

How to solve this problem?

Thanks.

HI!
i have numbers punched in my workbook upto three significant figures after the decimal point e.g; 650,250.897 which are proving a trouble to me.How can i select figures like this (i have such figures in many columns) and delete the digits after the deciaml (.897) and rounding the remaining digits to 650,251.i want deletion of that numbers and i cant use the ROUND function.
Thanks in advance.

GoodBYe to all

I'm trying to update the prices of several thousand products, so i'm useing the vlookup function, but the prices that are getting displayed have 5 or 6 numbers after the decimal. how can i condense this down into only 2 without using the format option.

The horizontal formula line is calculating money past two places after the
decimal point, yet showing only the two places (which is what I want to see).
The vertical formula then adds the results from the horizontal formula, but
it calculates beyond 2 places after the decimal, and causes the bottom line
figure to reflect an incorrect amount. Can this be overcome?

If I do per say =10/3 and the result would be 3.333333. How do I limit the output to only 2 decimal points. It's not an issue of not seeing the repeating 3 but I need it to display two places after the decimal.

I can use the =FIXED function but it will convert it to text.

Any other options to keep it as a number?

Thanks,
Pavel

DaddyLonglegs, Thank you for the help. I had done a cash on cash return ratio and it worked fantasticly. It got complicated but I got the result I was looking for.

Hello

I am merging data into a word doc but the merged numbers show many more
after the decimal point. I have formatted the cells to show only 1 or 2
numbers after the decimal. However, this does not prevent word adding them
back into the doc. Example 58.33 shows on excel but ends up as 58.333333 in
word.
Thanks

Hello,

I have a table where each column heading is a year. The numbers in the
table display 5 places after the decimal point. When I select all and format
the cells as a Number with only 3 decimal places, the years in the column
headings are also displayed with the three decimal places, like this:

1990.000 1991.000
13.478 13.902

How can I only format the numbers that already have places after the decimal
point - i.e.
Before
1990

Hello,

I have a table where each column heading is a year. The numbers in the
table display 5 places after the decimal point. When I select all and format
the cells as a Number with only 3 decimal places, the years in the column
headings are also displayed with the three decimal places, like this:

1990.000 1991.000
13.478 13.902

How can I only format the numbers that already have places after the decimal
point - i.e.
Before
1990 <- doesn't have decimal places
13.47895 <- has places after the decimal point

Thanks you!

I am looking to develop a code that will "realize" or look at all values in column a (actually just a certain range within Coloumn A) AFTER the decimal point when a cells contents contains numbers with a decimal point - and then write that decimal number in Coloumn B.

So for example, if a cell contains "14.3" in Cell A2. Then in B2 would be "0.3." ANother example would be "456.2496" in A4, then B4 would read "0.2496"

Please see uploaded excel spreadsheet to further explain my goal.

Thank you so much in advance.

Hi,

I have a survey of statistical data in an Excel-sheet
and I want in the right-most column the proportion of
subtotals in %.
This is all fine but I'm getting 7 trailing digits after the
decimal point and I want to reduce these to e.g. 2.
I know I'd make use of ##.## - but where do I put that
format?
Thanks in advance,
Jan

Hi,
I am trying to store and display a number with a scale of 10. The number that am pasting to the cell is : 9999999999999.9999999999. The custom format that I've chosen is " #,###,###,###,###.########## " The thousand's seperator gets displayed correctly but only 2 digits after the decimal point are displayed.
So the display changes to 9,999,999,999,999.99 thereby losing scale. Is there a limitation to the number of digits that can be displayed ? Or is it that the format I am using is inappropriate ?

I would be grateful for any pointers.

Thanks !

When I type 1.50 in any cell in any worksheet Excel displays it as 1. 50. I
have checked the decimal points in the format number, currency etc as well as
in Tools , options I even checked my Windows settings and re-installed Excel
but the problem still stays. PLEASE can somebody help.

Hi,

I have numeric data stored in Excel. A number like 25.12 is displayed and excel stores the real value of 25.123456789. I would like Excel to store no more than 3 digits after the decimal point. So 25.12 should be displayed and 25.123 should be stored. It is crucial that no more than 3 digits are stored by Excel. Otherwise, I can't do what I need to do after.

Ideas?

Hi I am trying to delete the numbers that follow the decimal point but I am struggling a bit. I have tried the replace ".*" option but it is joining the decimal not deleting it. For example :
A1 = 134.4678
A2 = 42.7777889
ect
I would like to be able to delete all the numbers after the decimal point so the outcome would be:
A1 = 134
B1 = 42
ect

Many thanks

Would like to do the following for our school,
In cell A1 there is a number, for instance: 12.12345

Based on that number, the 3rd number after the decimal is important.

If the 3rd number after the decimal is “x” then the contents returned in b2 is a text value ( like high school ).

ColA ColB
12.12145 middle school
12.12245 elementary school
12.12345 high school
12.12445 charter school
12.12545 special education
12.12645 advanced placement
12.12745 athletic departments
12.12845 special placements
12.12945 ais schools

So based on the value 0-9 that is in the 3rd number after the decimal, a text value in B2 is one of nine choices.

Additional requirement
1) The amount of numbers before the decimal point is variable
2) the number of values after the decimal point is equal to or larger than 3.
3) able to change the pivotal value after the decimal from 3 to another value ( say 2nd or 4th )

Your help is greatly appreciated.

undefined I need to change my workbook so that excel won't round decimals - i just want to have 2 places after the decimal point - how do i do that?

I am making up a character sheet for an online Dungeons and Dragons game, and I am just having alittle trouble getting one of the equations on my sheet to work out.

For those of you who are not familiar, the 'Base attack' statistic determines martial prowess, and it increases faster with some classes faster than others.

Fighters and their ilk get one point of BA every level including first
Clerics get three quarters of a point every level including first, which rounds down to 0 at first level
Mages get one half of a point every level, which would round down to 0 at first level.

Now on my sheet, the equations I am using are not working as they are taking the three quarter and one half points the other classes might get and counting them as a whole point.

I also have to contend with combining up to three different base attack totals on one sheet due to charecters being allowed to enter different classes with different BA scores.

My equation is

=(B4*B5)+(C4*C5)+(D4*D5)

Where B4, C4 and D4 are the cells that contain the number that shows what the BA progression is for the given class. And b5, c5 and d5 represent how many levels the character has in that class

B4 represents the full progression, and B5 equals the level, so if B5 was two, the first part would equate to one point of BA. That part works out fine so far. But where it gets to the C4*C5 parts, with c4 being .75. For some reason it is adding that all up as a whole point as well. D4* D5 is doing the same.

B4, C4 and D4 are formatted to number and displaying ftwo digits after the decimal to allow for .75 to fit
B5, C5 and D5 are formatted to number and displaying zero digits after the decimal

The cell that =(B4*B5)+(C4*C5)+(D4*D5) is in is also formatted to number and displaying zero digits after the decimal.

I'm sure it's really a simple correction that i'm missing, but any help would be appreciated.

I am wanting to carry out a calculation on only one part of a particular
result in a calculation made in a spreadsheet

ie if i have the result of "2.57945" how do i get excel to then only
select the digits after the decimal point for further calculations ie
".57945" bearing in mind the whole number will not always be a fixed
value.

--
gramsey
------------------------------------------------------------------------
gramsey's Profile: http://www.excelforum.com/member.php...o&userid=16143
View this thread: http://www.excelforum.com/showthread...hreadid=275773