CODEWITHSUNDEEP
if-statementgoogle-sheetsgoogle-sheets-formularoundingnested-if
user18587694
user18587694

Reputation:

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

Upvotes: 1

Views: 66

Answers (3)

player0
player0

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

enter image description here

Upvotes: 2

General Grievance
General Grievance

Reputation: 4988

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

Upvotes: 0

Avishai Barnoy
Avishai Barnoy

Reputation: 113

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)

enter image description here

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.

Upvotes: 0

Related Questions