I assume this may be an easy task, I have tried searching in the community, but can't find the one which I am looking for, so i have some numbers and want to round to a specific number,
| BEFORE | AFTER ROUNDED |
|---|---|
| 431 | 435 |
| 432 | 435 |
| 433 | 435 |
| 434 | 435 |
| 435 | 435 |
| 430 | 429 |
| 436 | 439 |
| 437 | 439 |
| 438 | 439 |
| 439 | 439 |
| 440 | 439 |
So, if the last digit is between 1-5 it will be i.e. 432 becomes 435; while 437 becomes 439. Also a sample data shown above, i have tried using MROUND or FLOOR Function but not getting through it, any way to get around this
CodePudding user response：
try:
=ARRAYFORMULA(
IF(REGEXMATCH(INT(X9:X14)&""; ". [1-5]$"); REGEXEXTRACT(INT(X9:X14)&""; "(.*)\d$")&5;
IF(REGEXMATCH(INT(X9:X14)&""; ". [0]$"); INT(X9:X14)-1; REGEXEXTRACT(INT(X9:X14)&""; "(.*)\d$")&9))*1)
CodePudding user response：
Ok, this was a little tricky.I worked mostly with rounddown. I started by getting the ones number, i.e., whether we are 6-9&0 or 1-5.
A1-rounddown(A1,-1)
I then moved to find which number to replace it with using an ifs to get all the cases.
=IFS(B1=0,9,B1<=5,5,A1-B1>5,9)
Afterwards I ran another ifs clause for the 3 cases where <>0, <=5, >5. I finally combined everything into a one-liner:
=if(A1-rounddown(A1,-1)<>0,rounddown(A1,-1) IFS(A1-rounddown(A1,-1)=0,9,A1-rounddown(A1,-1)<=5,5,A1-rounddown(A1,-1)>5,9),A1-1)
Hopefully this was understandable and helpful.
CodePudding user response：
- Round to nearest 5
- If (number - 1) mod 10 > 4 (i.e. 6, 7, 8, 9), subtract 1 to get 9
- Otherwise, leave it alone to not round
=ArrayFormula(CEILING(A:A/5)*5-(MOD(A:A-1,10)>4))