'How to round to a speicific number

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

Solution 1:^[1]

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)

Solution 2:^[2]

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.

Solution 3:^[3]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1
Solution 2	Avishai Barnoy
Solution 3	General Grievance

'How to round to a speicific number

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]

Solution 3:^[3]