+ Reply to Thread
Results 1 to 8 of 8

Problems with rounding down

  1. #1
    Registered User
    Join Date
    09-10-2012
    Location
    Essex, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Problems with rounding down

    Hi,

    I usually find what I am looking for in the forums but I am struggling to find any answers for my problem.

    I have set up a large spreadsheet to calculate quantites of concrete and brickwork to new build houses and I am having trouble with rounding in one of my cells.

    I am trying to calculate the area of bricks and blocks required to each house.

    As an example if I have 600mm of total brickwork I know I will require a 225mm trench block, 225mm block and 150mm of brickwork. (Blocks are always 225mm and bricks are always 75mm)

    To calculate the area of trench block required I need to take 600mm and deduct 225mm. I then need to divide this result and work out how many times 225mm goes in to it. In this example 600-225= 375. 375/225=1.667. So I need the answer to read 1.

    I have tried FLOOR and ROUNDDOWN but where the answer is an equal number, i.e 450/225=2, it rounds it down by an additional 1. If I use MROUND it rounds the number up when I need it to round the number down, i.e 375/225=1.667 would equal 2.

    I hope the above makes sense.

    Thanks in advance

    Marcus

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Problems with rounding down

    Try INT function

  3. #3
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Problems with rounding down

    Hi

    Try with INT function.

    =INT(375/225)

    =INT(450/225)
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Registered User
    Join Date
    09-10-2012
    Location
    Essex, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Problems with rounding down

    Thanks for the quick reply.

    I have tried this but it appears to be rounding down 1 too many.

    My formula reads =INT((BE4-0.225)/0.225) where BE4 is 0.675. This gives a result of 1 in excel but should be 2. 0.450/0.225=2.

    Thanks in advance

    Marcus

  5. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Problems with rounding down

    It should return 2. Check are those exactly that number (for example 0.675 is 0.675 and not 0.674999)

  6. #6
    Registered User
    Join Date
    09-10-2012
    Location
    Essex, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Problems with rounding down

    I have just deleted the calculation (BE4-0.225) and manually inserted 0.450 and the result changed to 2.

    Does the INT function not work properly when using the result of a calculation?

  7. #7
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Problems with rounding down

    it does but I reckon whatever is in BE4 is not calculating to exactly 0.450 as zbor said
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  8. #8
    Registered User
    Join Date
    09-10-2012
    Location
    Essex, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Problems with rounding down

    I have rounded the cell in which the 0.675 is located to 3 decimal places and it now appears to be working.

    Thanks you for your help.

    Much appreciated.

+ 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