+ Reply to Thread
Results 1 to 5 of 5

Help getting rid of decimal places

  1. #1
    Registered User
    Join Date
    04-08-2013
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    8

    Help getting rid of decimal places

    Originally posted this in the General Section think it belongs here:

    Help getting rid of decimal places

    Hi, newbie here, not very proficient in Excel, self taught from internet, so please bear this in mind when replying, many thanks, Mike.

    Using Excel 2010

    Workbook with 21 sheets, first sheet is the summary sheet (named SUMMARY) taking all it's content from the other sheets, named from 1 to 20, which are all laid out the same, but contain different data.

    My problem is that I want to get rid of decimal places that are being brought into the SUMMARY sheet

    In sheets named 1 to 20, I am using the following formula in H23 to arrive at a figure =IFERROR(100/(B5/B23),0) in H23

    B5 and B23 are always whole numbers on all sheets 1-20, result can be whole or contain decimal places which I round up in the cell to show no decimal places

    On sheet 1, B5 is 200 and B23 is 5, giving 2.50 as the answer, which is right, I have H23 set as a number cell with no decimal places so it rounds up to 3 which is what appears in the H23 cell.

    All of the above is correct and appearing as I want it on the sheet, the problem arises when I import into the SUMMARY sheet as follows:

    On the SUMMARY sheet in cell B59 I want the following to appear 5 - 3 but am getting 5 - 2.5 using the following formula

    ='1'!B23&" - "&'1'!H23

    The first part is fine as B23 will always be a whole number

    How can I stop it from bringing in the 2.5 and get it to bring in the rounded up 3 into cell B59

    I have cell B59 set as a number cell with no decimal places but it still appears as 2.5

    Also when I correct it as it appears a lot on the summary sheet, how do I get the following to happen

    ='1'!$B$23&" - "&'1'!$H$23

    if I fill the above across columns how to I get the two '1' s in the formula to become '2' then '3' as I fill across

    Should you reply please remember I am a dummy and know nothing about functions and I would be grateful if you reply in a simple manner

    Many thanks for any help you can give me.

    I got a reply but didn't work

    using ='1'!B23&" - "&int('1'!H23)

    Hi JieJenn, thanks for replying, tried it and it gets rid of the decimal places, but it rounds down or effectively just takes the first two numbers as in, the example I give above, the 2.5 becomes 2 when I use INT, I tried it on another cell where the result is 14.63 which appears on the sheet rounded up as 15 but when using INT it brings it into the summary sheet as 14 instead of the rounded up 15 that I want. Thanks again.

  2. #2
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Help getting rid of decimal places

    Replace the formula in b9 with :
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Also, you would have to manually change the sheet number the formula is referencing.
    Please click the * icon below if I have helped.

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,549

    Re: Help getting rid of decimal places

    will round() work for you

    =ROUND(h23,0)

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Help getting rid of decimal places

    Try using the round function on your formula.

    The basic problem is that when you apply a number format to a cell, and have it show 0 decimal places.
    That is only DISPLAYING the rounded value.
    The actual complete value with decimals still exists in the cell.
    And formulas that are referring to that cell see the actual value, not the value displayed by the cell's format.

    So for this formula, use
    =IFERROR(ROUND(100/(B5/B23),0),0)

    This will make the ACTUAL value rounded as if it was the cell's formatting doing the round.

  5. #5
    Registered User
    Join Date
    04-08-2013
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Help getting rid of decimal places

    Many, many thanks, Melvinrobb, etaf and Jonmo1, I used your formula Jonmo1, worked a treat, thanks again all.

    May I ask you about my second problem as given above, how do I get the two 1's in the formula ='1'!$B$23&" - "&'1'!$H$23 to increment automatically to become two 2's , 3's etc as I fill it across the rows in my sheet. if I fill it across using this formula the 1's stay as 1's all thw way across. thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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