Thread: 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.

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)

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.

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.
>
>

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.
> >
> >
>
>
>

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.
> >
> >
>
>

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.
> > >
> > >
> >
> >
>
>

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.
> > > >
> > > >
> > >
> > >
> >
> >
>
>