+ Reply to Thread
Results 1 to 6 of 6

Number of digits instead of decimal places

  1. #1
    Registered User
    Join Date
    07-13-2015
    Location
    United States
    MS-Off Ver
    2003
    Posts
    62

    Number of digits instead of decimal places

    I have a cell I'd like to display two digits, regardless of the number. So if it's less than 10, I'd like it to display 7.9 for insteance. But if it's 11, I don't want it to say 11.6, I don't want it to say 11.6, but rather 12

    Is this possible?

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Number of digits instead of decimal places

    I am not sure what rules are you trying to apply but maybe something like:

    =IF(A1<10,7.9,ROUNDUP(A1,0)) or =IF(A1<10,7.9,INT(A1)) or =IF(A1<10,7.9,INT(A1)+1)

  3. #3
    Registered User
    Join Date
    07-13-2015
    Location
    United States
    MS-Off Ver
    2003
    Posts
    62

    Re: Number of digits instead of decimal places

    Quote Originally Posted by PaulM100 View Post
    I am not sure what rules are you trying to apply but maybe something like:

    =IF(A1<10,7.9,ROUNDUP(A1,0)) or =IF(A1<10,7.9,INT(A1)) or =IF(A1<10,7.9,INT(A1)+1)
    No it's not that, it's that the cell is big enough for two digits (and a decimal point if need be). I'm interested in the tenths place when number is low but not when it's high. Right now I have it to display one decimal place but when it's over 10 it just shows ### because the length of the number is too long. So I want it to show:

    0.4 or
    4.8 or
    8.1 or
    9.9 or
    10 or
    11 or
    13 or
    14

  4. #4
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Number of digits instead of decimal places

    Try:

    =IF(A1<10,ROUND(A1,1),INT(A1))

  5. #5
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Number of digits instead of decimal places

    Perhaps a custom cell format of
    [<10]0.0;0
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  6. #6
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Number of digits instead of decimal places

    Attach a sample workbook with few examples Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Adusting decimal places based on greatest number of places in a series
    By anelson87 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-11-2022, 01:05 PM
  2. Replies: 12
    Last Post: 09-22-2017, 01:46 PM
  3. [SOLVED] Need to limit the number of decimal places of a number in a text string
    By Turbo Dog in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-11-2013, 07:47 PM
  4. [SOLVED] copy number with 3 or more decimal places and paste the actual value as 2 decimal number
    By k1dr0ck in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-29-2013, 12:57 AM
  5. Vba to format number of decimal places dependant on number in another cell
    By wonderdunder in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-10-2012, 09:33 AM
  6. number of decimal places
    By Geordie in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-22-2010, 03:06 AM
  7. Paste two decimal number in excel without extra decimal places appearing
    By jeffery_frick in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-13-2009, 07:49 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1