#1
|
|||
|
|||
Custom rounding up
I regularly need to perform this manual rounding operation, and would love to find an automated solution:
If the decimal of the number is between .0 and .4, it's rounded up to the next integer. If the decimal of the number is between .5 and .9, it's rounded up to one integer larger than the next integer. Examples: 1.3 → 2 4.5 → 6 8.9 → 10 11 → 12 A formula would be fine - I could just paste the formula in the adjacent column. Alternatively, I'd be happy to highlight the relevant cells and run a macro on them. Thanks in advance! |
#2
|
|||
|
|||
=if(mod(a1,1)<=0.4,int(a1)+1,if((mod(a1,1)>0.4)*(m od(a1,1)<=0.9),int(a1)+2,""))
|
#3
|
|||
|
|||
Thank you.
I edited the formula to keep zero as zero: =IF(A1=0,0,IF(MOD(A1,1)<=0.4,INT(A1)+1,IF((MOD(A1, 1)>0.4)*(m od(A1,1)<=0.9),INT(A1)+2,""))) I've "stored" it in cell B1. Unfortunately I'm getting strange results when I copy-paste it into other cells. Some work, and some do not! I copied the formula into cells to the right of the cells containing the original values. Here is the result: 6.7 #NAME? 6.6 #NAME? 4.1 5 5.2 6 0 0 0 0 9 10 6.9 #NAME? 10.7 #NAME? 6.7 #NAME? 9.4 10 4.2 5 0 0 0 0 7 8 0 0 2.5 #NAME? 14.5 #NAME? 1.7 #NAME? 0 0 7.8 #NAME? 8.2 9 6.6 #NAME? 0 0 0 0 0 0 0 0 9.7 #NAME? 3.2 4 4.6 #NAME? 9.8 #NAME? Any ideas?! Last edited by paulkaye; 09-01-2016 at 08:12 AM. Reason: pasted values came out distorted |
#4
|
|||
|
|||
It seems to work if I have understood you correct.
|
#5
|
||||
|
||||
=IF(A1=0,0,IF(MOD(A1,1)<=0.4,INT(A1)+1,IF((MOD(A1, 1)>0.4)*(mod(A1,1)<=0.9),INT(A1)+2,"")))
There seems to have been a typo in the mod function you pasted ( m od instead of mod), or the forum gremlins at work :-)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post Last edited by Pecoflyer; 09-01-2016 at 09:21 AM. Reason: Corrected after a post by NoSpark |
#6
|
||||
|
||||
Just for the fun =CEILING(A1,1)+OR(MOD(A1,1)>0.4,MOD(A1,1)=0)
(tested with OOffice)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#7
|
|||
|
|||
@ Peco
Don't be too hasty to call that a typo. This forum doesn't always display what's typed or pasted into the reply box. Wouldn't let me eliminate the space it added to a formula no matter how I tried. https://www.msofficeforums.com/excel...tml#post103248 |
#8
|
||||
|
||||
Now that you mention it, I seem to remember that editing problem.Strange. I will amend my post accordingly)
Thanks
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#9
|
|||
|
|||
That space in the original formula was the problem. Works perfectly now. Thanks, Xor, and everyone else!
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel Seems to be Rounding Twice Rather Than Once | melissapete24 | Excel | 5 | 08-27-2016 12:51 AM |
Time Calc with rounding. | billreed7 | Excel | 5 | 07-03-2016 06:43 PM |
Rounding to nearest 0.25 | Guloluseus | Excel | 5 | 10-27-2015 11:28 PM |
Rounding values inconsistent | required_username | Excel | 10 | 06-05-2014 05:35 AM |
Custom formatting code - rounding problem | venkys4u | Excel | 1 | 08-14-2012 07:45 PM |