|
Rounding to nearest 9
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 |
Rounding to nearest 9
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 |
Rounding to nearest 9
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 |
Rounding to nearest 9
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 |
Rounding to nearest 9
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 |
Rounding to nearest 9
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 |
Rounding to nearest 9
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 |
Rounding to nearest 9
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 |
Rounding to nearest 9
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 |
Rounding to nearest 9
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 |
Rounding to nearest 9
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 |
Rounding to nearest 9
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 |
Rounding to nearest 9
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 |
Rounding to nearest 9
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 |
Rounding to nearest 9
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 |
Rounding to nearest 9
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 |
Rounding to nearest 9
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 |
Rounding to nearest 9
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 |
Rounding to nearest 9
=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 |
Rounding to nearest 9
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 |
Rounding to nearest 9
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 |
| All times are GMT +1. The time now is 08:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com