I have the following =(E5+G5) * 1.25 which results in $1446.25. I would
like it to be rounded to $1449.00. How could I do this? Thanks!
--
Gary in Michigan, USA

suppose u have result in A1,
try =CEILING(A1,9)

On Jun 5, 10:11*pm, GaryS wrote:
I have the following =(E5+G5) * 1.25 * which results in $1446.25. *I would
like it to be rounded to $1449.00. *How could I do this? *Thanks!
--
Gary in Michigan, USA

Hi Gary

One way
=CEILING((E5+G5)*1.25,3)

--
Regards
Roger Govier

"GaryS" wrote in message
...
I have the following =(E5+G5) * 1.25 which results in $1446.25. I would
like it to be rounded to $1449.00. How could I do this? Thanks!
--
Gary in Michigan, USA

One possible

=MAX(ROUND(((E5+G5) * 1.25)/10,0)*10-1,0)


--


Regards,


Peo Sjoblom


"GaryS" wrote in message
...
I have the following =(E5+G5) * 1.25 which results in $1446.25. I would
like it to be rounded to $1449.00. How could I do this? Thanks!
--
Gary in Michigan, USA

I read the OP's subject which is misleading but if he want to roundup
1446.25 to 1449
then I would assume it would be

=CEILING(((E5+G5)*1.25,10)-1

or rather this to take care of zeros


--


Regards,


Peo Sjoblom





"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi Gary

One way
=CEILING((E5+G5)*1.25,3)

--
Regards
Roger Govier

"GaryS" wrote in message
...
I have the following =(E5+G5) * 1.25 which results in $1446.25. I
would
like it to be rounded to $1449.00. How could I do this? Thanks!
--
Gary in Michigan, USA

Forgot the last formula

=MAX(CEILING((E5+G5)*1.25,10)-1,0)



--


Regards,


Peo Sjoblom


"Peo Sjoblom" wrote in message
...
I read the OP's subject which is misleading but if he want to roundup
1446.25 to 1449
then I would assume it would be

=CEILING(((E5+G5)*1.25,10)-1

or rather this to take care of zeros


--


Regards,


Peo Sjoblom





"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi Gary

One way
=CEILING((E5+G5)*1.25,3)

--
Regards
Roger Govier

"GaryS" wrote in message
...
I have the following =(E5+G5) * 1.25 which results in $1446.25. I
would
like it to be rounded to $1449.00. How could I do this? Thanks!
--
Gary in Michigan, USA

Hi Guys,

Thanks. This one comes the closest, but it rounds up OR down.
=MAX(ROUND(((E5+G5) * 1.25)/10,0)*10-1,0)

I only want it to round up to the nearest 9. In other words if the results
are $3243.00 I want it rounded to $3249.00.

Thanks!
--
Gary in Michigan, USA


"Peo Sjoblom" wrote:

One possible

=MAX(ROUND(((E5+G5) * 1.25)/10,0)*10-1,0)


--


Regards,


Peo Sjoblom


"GaryS" wrote in message
...
I have the following =(E5+G5) * 1.25 which results in $1446.25. I would
like it to be rounded to $1449.00. How could I do this? Thanks!
--
Gary in Michigan, USA

Peo,

This one rounds $3250 down to $3249. I need it to round $3250 to $3259.

Thanks so much for your help.
--
Gary in Michigan, USA


"Peo Sjoblom" wrote:

One possible

=MAX(ROUND(((E5+G5) * 1.25)/10,0)*10-1,0)


--


Regards,


Peo Sjoblom


"GaryS" wrote in message
...
I have the following =(E5+G5) * 1.25 which results in $1446.25. I would
like it to be rounded to $1449.00. How could I do this? Thanks!
--
Gary in Michigan, USA

You didn't say what you wanted to happen for values above the "9 value" but
less than the "0 value"; for example, what do you want $3249.50 to "round"
to?

Depending on your answer to my question, this formula **may** do what you
want...

=10*ROUNDUP((A1+(MOD(A1,10)=0))/10,0)-1

Rick



"GaryS" wrote in message
...
Peo,

This one rounds $3250 down to $3249. I need it to round $3250 to $3259.

Thanks so much for your help.
--
Gary in Michigan, USA


"Peo Sjoblom" wrote:

One possible

=MAX(ROUND(((E5+G5) * 1.25)/10,0)*10-1,0)


--


Regards,


Peo Sjoblom


"GaryS" wrote in message
...
I have the following =(E5+G5) * 1.25 which results in $1446.25. I
would
like it to be rounded to $1449.00. How could I do this? Thanks!
--
Gary in Michigan, USA

It was because you stated in your subject line that you wanted nearest which
is what I gave you, but after looking at your example it was obvious you
wanted to round up

=MAX(CEILING((E5+G5)*1.25,10)-1,0)

will round up


--


Regards,


Peo Sjoblom


"GaryS" wrote in message
...
Peo,

This one rounds $3250 down to $3249. I need it to round $3250 to $3259.

Thanks so much for your help.
--
Gary in Michigan, USA


"Peo Sjoblom" wrote:

One possible

=MAX(ROUND(((E5+G5) * 1.25)/10,0)*10-1,0)


--


Regards,


Peo Sjoblom


"GaryS" wrote in message
...
I have the following =(E5+G5) * 1.25 which results in $1446.25. I
would
like it to be rounded to $1449.00. How could I do this? Thanks!
--
Gary in Michigan, USA

You didn't say what you wanted to happen for values above the "9 value"
but less than the "0 value"; for example, what do you want $3249.50 to
"round" to?

Depending on your answer to my question, this formula **may** do what you
want...

=10*ROUNDUP((A1+(MOD(A1,10)=0))/10,0)-1

Of course you will want to replace my A1 references with (E5+G5)*1.25.

Rick

Rick, this is perfect. Thank you.

--
Gary in Michigan, USA


"Rick Rothstein (MVP - VB)" wrote:

You didn't say what you wanted to happen for values above the "9 value"
but less than the "0 value"; for example, what do you want $3249.50 to
"round" to?

Depending on your answer to my question, this formula **may** do what you
want...

=10*ROUNDUP((A1+(MOD(A1,10)=0))/10,0)-1

Of course you will want to replace my A1 references with (E5+G5)*1.25.

Rick

Hi Peo,

For some reason this solution still does not round up. I apologise for
forgetting to mention rounding up. The solution Rick provided does work for
me. Thank you so much for your help.
--
Gary in Michigan, USA


"Peo Sjoblom" wrote:

It was because you stated in your subject line that you wanted nearest which
is what I gave you, but after looking at your example it was obvious you
wanted to round up

=MAX(CEILING((E5+G5)*1.25,10)-1,0)

will round up


--


Regards,


Peo Sjoblom


"GaryS" wrote in message
...
Peo,

This one rounds $3250 down to $3249. I need it to round $3250 to $3259.

Thanks so much for your help.
--
Gary in Michigan, USA


"Peo Sjoblom" wrote:

One possible

=MAX(ROUND(((E5+G5) * 1.25)/10,0)*10-1,0)


--


Regards,


Peo Sjoblom


"GaryS" wrote in message
...
I have the following =(E5+G5) * 1.25 which results in $1446.25. I
would
like it to be rounded to $1449.00. How could I do this? Thanks!
--
Gary in Michigan, USA

This should work

=MAX(CEILING(A1+1,10)-1,0)


note that Rick's solution does not round up when the last integer is 9 and
there are decimals,
for instance 9.98, it rounds that to 9 or 1329.95 is 1329. Given your
preferences
shouldn't those be rounded to 19 and 1339 respectively?


--


Regards,


Peo Sjoblom




"GaryS" wrote in message
...
Hi Peo,

For some reason this solution still does not round up. I apologise for
forgetting to mention rounding up. The solution Rick provided does work
for
me. Thank you so much for your help.
--
Gary in Michigan, USA


"Peo Sjoblom" wrote:

It was because you stated in your subject line that you wanted nearest
which
is what I gave you, but after looking at your example it was obvious you
wanted to round up

=MAX(CEILING((E5+G5)*1.25,10)-1,0)

will round up


--


Regards,


Peo Sjoblom


"GaryS" wrote in message
...
Peo,

This one rounds $3250 down to $3249. I need it to round $3250 to
$3259.

Thanks so much for your help.
--
Gary in Michigan, USA


"Peo Sjoblom" wrote:

One possible

=MAX(ROUND(((E5+G5) * 1.25)/10,0)*10-1,0)


--


Regards,


Peo Sjoblom


"GaryS" wrote in message
...
I have the following =(E5+G5) * 1.25 which results in $1446.25. I
would
like it to be rounded to $1449.00. How could I do this? Thanks!
--
Gary in Michigan, USA

note that Rick's solution does not round up when the last integer is 9 and
there are decimals,
for instance 9.98, it rounds that to 9 or 1329.95 is 1329. Given your
preferences
shouldn't those be rounded to 19 and 1339 respectively?

Yep, I specifically pointed out that "flaw" prior to showing the formula to
the OP as he did not mention how he wanted to handle that situation.

Rick

Sorry, didn't see that. Regardless, my formula takes care of that "flaw"


--


Regards,


Peo Sjoblom


"Rick Rothstein (MVP - VB)" wrote in
message ...
note that Rick's solution does not round up when the last integer is 9
and there are decimals,
for instance 9.98, it rounds that to 9 or 1329.95 is 1329. Given your
preferences
shouldn't those be rounded to 19 and 1339 respectively?

Yep, I specifically pointed out that "flaw" prior to showing the formula
to the OP as he did not mention how he wanted to handle that situation.

Rick

Peo (and Rick),

You guys are right in that I want 329.50 to round to 339.00, and I didn't
point that out. Also I neglected originally to say that I want to always
round up not down.

Peo, when I use your solution substituing my situation for your A1 reference
I get an error that I'm missing an arugment. Here is what I put down:

=MAX(CEILING((E5+G5)*1.25)+1,10)-1,0)

I'm sure I'm just blind. What am I missing? Thanks!
--
Gary in Michigan, USA


"Peo Sjoblom" wrote:

Sorry, didn't see that. Regardless, my formula takes care of that "flaw"


--


Regards,


Peo Sjoblom


"Rick Rothstein (MVP - VB)" wrote in
message ...
note that Rick's solution does not round up when the last integer is 9
and there are decimals,
for instance 9.98, it rounds that to 9 or 1329.95 is 1329. Given your
preferences
shouldn't those be rounded to 19 and 1339 respectively?

Yep, I specifically pointed out that "flaw" prior to showing the formula
to the OP as he did not mention how he wanted to handle that situation.

Rick

The reason I put "flaw" in quotes is because I was not completely sure
whether the OP wanted the rounding my formula does or the one yours does. It
seems strange to me that 1329.01 through 1329.99 (**especially** the
beginning of that range) should be rounded up to $1339, hence the formula I
posted. It may well turn out, however, that your rounding method is the
actual one the OP wants... we will just have to wait for him to come back to
this thread to see.

Rick


"Peo Sjoblom" wrote in message
...
Sorry, didn't see that. Regardless, my formula takes care of that "flaw"

Regards,

Peo Sjoblom


"Rick Rothstein (MVP - VB)" wrote in
message ...
note that Rick's solution does not round up when the last integer is 9
and there are decimals,
for instance 9.98, it rounds that to 9 or 1329.95 is 1329. Given your
preferences
shouldn't those be rounded to 19 and 1339 respectively?

Yep, I specifically pointed out that "flaw" prior to showing the formula
to the OP as he did not mention how he wanted to handle that situation.

Rick

=MAX(CEILING((E5+G5)*1.25+1,10)-1,0)

but it is moot anyway if you want 9.5 to round down to 9 and not to 19


--


Regards,


Peo Sjoblom


"GaryS" wrote in message
...
Peo (and Rick),

You guys are right in that I want 329.50 to round to 339.00, and I didn't
point that out. Also I neglected originally to say that I want to always
round up not down.

Peo, when I use your solution substituing my situation for your A1
reference
I get an error that I'm missing an arugment. Here is what I put down:

=MAX(CEILING((E5+G5)*1.25)+1,10)-1,0)

I'm sure I'm just blind. What am I missing? Thanks!
--
Gary in Michigan, USA


"Peo Sjoblom" wrote:

Sorry, didn't see that. Regardless, my formula takes care of that "flaw"


--


Regards,


Peo Sjoblom


"Rick Rothstein (MVP - VB)" wrote
in
message ...
note that Rick's solution does not round up when the last integer is 9
and there are decimals,
for instance 9.98, it rounds that to 9 or 1329.95 is 1329. Given your
preferences
shouldn't those be rounded to 19 and 1339 respectively?

Yep, I specifically pointed out that "flaw" prior to showing the
formula
to the OP as he did not mention how he wanted to handle that situation.

Rick

Peo - Your solution is EXACTLY what I DO want. I ALWAYS want to round UP to
the next $9.00. Thank you very much!
--
Gary in Michigan, USA


"Peo Sjoblom" wrote:

=MAX(CEILING((E5+G5)*1.25+1,10)-1,0)

but it is moot anyway if you want 9.5 to round down to 9 and not to 19


--


Regards,


Peo Sjoblom


"GaryS" wrote in message
...
Peo (and Rick),

You guys are right in that I want 329.50 to round to 339.00, and I didn't
point that out. Also I neglected originally to say that I want to always
round up not down.

Peo, when I use your solution substituing my situation for your A1
reference
I get an error that I'm missing an arugment. Here is what I put down:

=MAX(CEILING((E5+G5)*1.25)+1,10)-1,0)

I'm sure I'm just blind. What am I missing? Thanks!
--
Gary in Michigan, USA


"Peo Sjoblom" wrote:

Sorry, didn't see that. Regardless, my formula takes care of that "flaw"


--


Regards,


Peo Sjoblom


"Rick Rothstein (MVP - VB)" wrote
in
message ...
note that Rick's solution does not round up when the last integer is 9
and there are decimals,
for instance 9.98, it rounds that to 9 or 1329.95 is 1329. Given your
preferences
shouldn't those be rounded to 19 and 1339 respectively?

Yep, I specifically pointed out that "flaw" prior to showing the
formula
to the OP as he did not mention how he wanted to handle that situation.

Rick

Okay, the OP wrote back in another sub-thread... there is no longer any
question... it is not a "flaw", rather it is a **flaw**.<g

Good reading of the OP's intent there Peo (although I still think it strange
to round 1329.01 to 1339 rather than 1329).

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
The reason I put "flaw" in quotes is because I was not completely sure
whether the OP wanted the rounding my formula does or the one yours does.
It seems strange to me that 1329.01 through 1329.99 (**especially** the
beginning of that range) should be rounded up to $1339, hence the formula
I posted. It may well turn out, however, that your rounding method is the
actual one the OP wants... we will just have to wait for him to come back
to this thread to see.

Rick


"Peo Sjoblom" wrote in message
...
Sorry, didn't see that. Regardless, my formula takes care of that "flaw"

Regards,

Peo Sjoblom


"Rick Rothstein (MVP - VB)" wrote
in message ...
note that Rick's solution does not round up when the last integer is 9
and there are decimals,
for instance 9.98, it rounds that to 9 or 1329.95 is 1329. Given your
preferences
shouldn't those be rounded to 19 and 1339 respectively?

Yep, I specifically pointed out that "flaw" prior to showing the formula
to the OP as he did not mention how he wanted to handle that situation.

Rick