+ Reply to Thread
Results 1 to 8 of 8

rounding to nearest hundred dollar in Excel

  1. #1
    Diane
    Guest

    rounding to nearest hundred dollar in Excel

    User wants to round up to nearest $100 dollar figure if it's over 50, or down
    to nearest $100 figure if it's under 50. For example, if they enter dollar
    figure of $1234.00, they want it rounded down to the nearest $100 dollar
    amount, which would be $1,200.00. Thanks.

  2. #2
    Registered User
    Join Date
    10-05-2005
    Posts
    19
    The round function will do this. =ROUND(X,Y)
    X is the number (cell reference) you want rounding
    Y is the number of decimal places you want it to round to. If you want something rounded to 3 decimal places, Y would be 3. Y can take negative values, such as -1, which rounds to the nearest ten and -2, which rounds to the nearest hundred, which you are after.
    Assuming the cell you want rounding is F10, the equation is
    =ROUND(F10,-2)

  3. #3
    Peo Sjoblom
    Guest

    Re: rounding to nearest hundred dollar in Excel

    50000? Since you say round up to nearest 100 if over 50 shouldn't 1234 round
    up to 1300? Or did you mean 50000?

    Regardsless round to nearest 100 is ROUND(x,100) round up is
    CEILING(x,100)

    so you can set it the way you want by testing the condition

    =IF(x>50,formula1,formula2

    Regards,

    Peo Sjoblom
    "Diane" <Diane@discussions.microsoft.com> wrote in message
    news:3F4FEA12-8214-47AE-BE42-DB400EE3ECE7@microsoft.com...
    > User wants to round up to nearest $100 dollar figure if it's over 50, or

    down
    > to nearest $100 figure if it's under 50. For example, if they enter dollar
    > figure of $1234.00, they want it rounded down to the nearest $100 dollar
    > amount, which would be $1,200.00. Thanks.




  4. #4
    Bob Phillips
    Guest

    Re: rounding to nearest hundred dollar in Excel

    Round to the nearest 100 is

    =ROUND(x,-2)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Peo Sjoblom" <terre08@mvps.org> wrote in message
    news:ONKwUHM0FHA.3780@TK2MSFTNGP12.phx.gbl...
    > 50000? Since you say round up to nearest 100 if over 50 shouldn't 1234

    round
    > up to 1300? Or did you mean 50000?
    >
    > Regardsless round to nearest 100 is ROUND(x,100) round up is
    > CEILING(x,100)
    >
    > so you can set it the way you want by testing the condition
    >
    > =IF(x>50,formula1,formula2
    >
    > Regards,
    >
    > Peo Sjoblom
    > "Diane" <Diane@discussions.microsoft.com> wrote in message
    > news:3F4FEA12-8214-47AE-BE42-DB400EE3ECE7@microsoft.com...
    > > User wants to round up to nearest $100 dollar figure if it's over 50, or

    > down
    > > to nearest $100 figure if it's under 50. For example, if they enter

    dollar
    > > figure of $1234.00, they want it rounded down to the nearest $100 dollar
    > > amount, which would be $1,200.00. Thanks.

    >
    >




  5. #5
    Diane
    Guest

    Re: rounding to nearest hundred dollar in Excel

    Thanks Bob,

    User tried it, and it works!

    Diane

    "Bob Phillips" wrote:

    > Round to the nearest 100 is
    >
    > =ROUND(x,-2)
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Peo Sjoblom" <terre08@mvps.org> wrote in message
    > news:ONKwUHM0FHA.3780@TK2MSFTNGP12.phx.gbl...
    > > 50000? Since you say round up to nearest 100 if over 50 shouldn't 1234

    > round
    > > up to 1300? Or did you mean 50000?
    > >
    > > Regardsless round to nearest 100 is ROUND(x,100) round up is
    > > CEILING(x,100)
    > >
    > > so you can set it the way you want by testing the condition
    > >
    > > =IF(x>50,formula1,formula2
    > >
    > > Regards,
    > >
    > > Peo Sjoblom
    > > "Diane" <Diane@discussions.microsoft.com> wrote in message
    > > news:3F4FEA12-8214-47AE-BE42-DB400EE3ECE7@microsoft.com...
    > > > User wants to round up to nearest $100 dollar figure if it's over 50, or

    > > down
    > > > to nearest $100 figure if it's under 50. For example, if they enter

    > dollar
    > > > figure of $1234.00, they want it rounded down to the nearest $100 dollar
    > > > amount, which would be $1,200.00. Thanks.

    > >
    > >

    >
    >
    >


  6. #6
    Peo Sjoblom
    Guest

    Re: rounding to nearest hundred dollar in Excel

    Doh!

    Peo


    "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
    news:O4jLLMM0FHA.4032@TK2MSFTNGP15.phx.gbl...
    > Round to the nearest 100 is
    >
    > =ROUND(x,-2)
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Peo Sjoblom" <terre08@mvps.org> wrote in message
    > news:ONKwUHM0FHA.3780@TK2MSFTNGP12.phx.gbl...
    > > 50000? Since you say round up to nearest 100 if over 50 shouldn't 1234

    > round
    > > up to 1300? Or did you mean 50000?
    > >
    > > Regardsless round to nearest 100 is ROUND(x,100) round up is
    > > CEILING(x,100)
    > >
    > > so you can set it the way you want by testing the condition
    > >
    > > =IF(x>50,formula1,formula2
    > >
    > > Regards,
    > >
    > > Peo Sjoblom
    > > "Diane" <Diane@discussions.microsoft.com> wrote in message
    > > news:3F4FEA12-8214-47AE-BE42-DB400EE3ECE7@microsoft.com...
    > > > User wants to round up to nearest $100 dollar figure if it's over 50,

    or
    > > down
    > > > to nearest $100 figure if it's under 50. For example, if they enter

    > dollar
    > > > figure of $1234.00, they want it rounded down to the nearest $100

    dollar
    > > > amount, which would be $1,200.00. Thanks.

    > >
    > >

    >
    >




  7. #7
    Bob Phillips
    Guest

    Re: rounding to nearest hundred dollar in Excel

    You were thinking ahead of yourself Peo :-)

    "Peo Sjoblom" <terre08@mvps.org> wrote in message
    news:eLH4aeM0FHA.2348@TK2MSFTNGP15.phx.gbl...
    > Doh!
    >
    > Peo
    >
    >
    > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
    > news:O4jLLMM0FHA.4032@TK2MSFTNGP15.phx.gbl...
    > > Round to the nearest 100 is
    > >
    > > =ROUND(x,-2)
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > >
    > > "Peo Sjoblom" <terre08@mvps.org> wrote in message
    > > news:ONKwUHM0FHA.3780@TK2MSFTNGP12.phx.gbl...
    > > > 50000? Since you say round up to nearest 100 if over 50 shouldn't 1234

    > > round
    > > > up to 1300? Or did you mean 50000?
    > > >
    > > > Regardsless round to nearest 100 is ROUND(x,100) round up is
    > > > CEILING(x,100)
    > > >
    > > > so you can set it the way you want by testing the condition
    > > >
    > > > =IF(x>50,formula1,formula2
    > > >
    > > > Regards,
    > > >
    > > > Peo Sjoblom
    > > > "Diane" <Diane@discussions.microsoft.com> wrote in message
    > > > news:3F4FEA12-8214-47AE-BE42-DB400EE3ECE7@microsoft.com...
    > > > > User wants to round up to nearest $100 dollar figure if it's over

    50,
    > or
    > > > down
    > > > > to nearest $100 figure if it's under 50. For example, if they enter

    > > dollar
    > > > > figure of $1234.00, they want it rounded down to the nearest $100

    > dollar
    > > > > amount, which would be $1,200.00. Thanks.
    > > >
    > > >

    > >
    > >

    >
    >




  8. #8
    Peo Sjoblom
    Guest

    Re: rounding to nearest hundred dollar in Excel

    You forgot "again" at the end

    )

    Peo


    "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
    news:ewFTTCN0FHA.1264@tk2msftngp13.phx.gbl...
    > You were thinking ahead of yourself Peo :-)
    >
    > "Peo Sjoblom" <terre08@mvps.org> wrote in message
    > news:eLH4aeM0FHA.2348@TK2MSFTNGP15.phx.gbl...
    > > Doh!
    > >
    > > Peo
    > >
    > >
    > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
    > > news:O4jLLMM0FHA.4032@TK2MSFTNGP15.phx.gbl...
    > > > Round to the nearest 100 is
    > > >
    > > > =ROUND(x,-2)
    > > >
    > > > --
    > > >
    > > > HTH
    > > >
    > > > RP
    > > > (remove nothere from the email address if mailing direct)
    > > >
    > > >
    > > > "Peo Sjoblom" <terre08@mvps.org> wrote in message
    > > > news:ONKwUHM0FHA.3780@TK2MSFTNGP12.phx.gbl...
    > > > > 50000? Since you say round up to nearest 100 if over 50 shouldn't

    1234
    > > > round
    > > > > up to 1300? Or did you mean 50000?
    > > > >
    > > > > Regardsless round to nearest 100 is ROUND(x,100) round up is
    > > > > CEILING(x,100)
    > > > >
    > > > > so you can set it the way you want by testing the condition
    > > > >
    > > > > =IF(x>50,formula1,formula2
    > > > >
    > > > > Regards,
    > > > >
    > > > > Peo Sjoblom
    > > > > "Diane" <Diane@discussions.microsoft.com> wrote in message
    > > > > news:3F4FEA12-8214-47AE-BE42-DB400EE3ECE7@microsoft.com...
    > > > > > User wants to round up to nearest $100 dollar figure if it's over

    > 50,
    > > or
    > > > > down
    > > > > > to nearest $100 figure if it's under 50. For example, if they

    enter
    > > > dollar
    > > > > > figure of $1234.00, they want it rounded down to the nearest $100

    > > dollar
    > > > > > amount, which would be $1,200.00. 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