One cell formula

[JimDina]

Hello,

I am looking to create a formula for one cell that basically rounds any number with a decimal (15.3) to the next multiple of 5.

So if I enter 15.3 in cell 1a, I'd like that number to be rounded up to 20 and display in the cell 2b.

Example:

15.0 = 3 (15 divided by 5 = 3)
15.0 entered in cell A1
3 automatically displays in cell 2A

33.5 = 7 (33.5 rounded up to 35 divided by 5 = 7
33.5 entered in cell A1
7 automatically displays in cell 2A

I hope I explained it properly, any help is appreciated.

Thx
Jim

 

[jtakw]

Hi,

Like this?

Book3.xlsx
	A	B
1	15	33.5
2	3	7
Sheet958

Cell Formulas
Range	Formula
A2:B2	A2	=MROUND(A1,5)/5

 

[JimDina]

Hi,

Like this?

Book3.xlsx
	A	B
1	15	33.5
2	3	7
Sheet958

Cell Formulas
Range	Formula
A2:B2	A2	=MROUND(A1,5)/5

Kind of, I’d like to only use 2 cells, entering the number in 1A, then the result shows in 1B.

 

[jtakw]

Kind of, I’d like to only use 2 cells, entering the number in 1A, then the result shows in 1B.

I'm just showing you 2 samples like in your OP, take 1 out.

Book3.xlsx
	A	B
1	33.5	7
Sheet958

Cell Formulas
Range	Formula
B1	B1	=MROUND(A1,5)/5

Also, should 31 be rounded to 30 or 35?

 

[JimDina]

I'm just showing you 2 samples like in your OP, take 1 out.

Book3.xlsx
	A
1	33.5
2	7
Sheet958

Cell Formulas
Range	Formula
A2	A2	=MROUND(A1,5)/5

Excellent, that will work, truly appreciate your help!

 

[jtakw]

I added a question above: Should 31 be rounded to 30 or 35 ?

 

[JimDina]

I added a question above: Should 31 be rounded to 30 or 35 ?

So a number will be entered with a decimal, like 30.2, 35.7, those numbers need to be rounded up to 35 and 40 respectively. Only one number will be entered in the cell.

 

[jtakw]

Use this instead, CEILING Always rounds Up, MROUND goes both ways 4/5

Book3.xlsx
	A	B
1	30.2	7
Sheet958

Cell Formulas
Range	Formula
B1	B1	=CEILING(A1,5)/5

 

[JimDina]

Use this instead, CEILING Always rounds Up, MROUND goes both ways 4/5

Book3.xlsx
	A	B
1	30.2	7
Sheet958

Cell Formulas
Range	Formula
B1	B1	=CEILING(A1,5)/5

Awesome, thx!

 

[jtakw]

You're welcome.