+ Reply to Thread
Results 1 to 3 of 3

Need to limit the number of decimal places of a number in a text string

  1. #1
    Registered User
    Join Date
    10-03-2011
    Location
    West of Laramie
    MS-Off Ver
    Excel 2007
    Posts
    12

    Need to limit the number of decimal places of a number in a text string

    Below is the formula I am using to test if an engine has run more than 336 hours. The first IF statement keeps the cell clear if there aren't any hours recorded on the 'DIV3 hours' worksheet. The next IF checks another worksheet called 'Logic' to see if a button has been used to indicate that the engine test has been completed. The third IF statement checks the number of hours on 'DIV3 hours' and then fills the cell with some text. When the hours are summed up in a range I found out that the data on the 'DIV3 hours' page has many decimal places. It was already formatted for no decimal places.


    =IF(SUM('DIV3 hours'!K42:N42)<1,"",IF(Logic!C63=TRUE,"Completed",IF(SUM('DIV3 hours'!K42:N42)>336,"Need Test",SUM('DIV3 hours'!K42:N42)&" of 336hrs")))

    This returned "8.5850000000001 of 336hrs" in the cell. Is ther a way I can get it to only display whole numbers?

  2. #2
    Registered User
    Join Date
    10-03-2011
    Location
    West of Laramie
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Need to limit the number of decimal places of a number in a text string

    I found out how to do it from a link to another thread.

    =IF(SUM('DIV3 hours'!K42:N42)<1,"",IF(Logic!C63=TRUE,"Completed",IF(SUM('DIV3 hours'!K42:N42)>336,"Need Test",ROUND(SUM('DIV3 hours'!K42:N42),0)&" of 336hrs")))

    Using ROUND(formula),0 the zero is the number od decimal places that will be shown.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,456

    Re: Need to limit the number of decimal places of a number in a text string

    Can't test it, no data, no workbook, but try:

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ 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. [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
  2. number to text but with only two decimal places
    By mjwillyone in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-19-2010, 09:34 PM
  3. limit my number to 2 decimal places
    By Eee in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 12-19-2007, 09:12 AM
  4. Setting the number of decimal places for a text box.
    By Aaron1978 in forum Excel General
    Replies: 3
    Last Post: 03-08-2006, 01:10 PM
  5. Replies: 5
    Last Post: 12-08-2005, 04:25 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