Rounding

[Gary in Guernsey]

Hi Team,
I have racked my brain trying to sort this, I have columns of numbers that are all integers. What i am trying to do is, if the last figure in the number is 1,2,3,or 4 then I need to set the number to round down to 0-1, so if the number is 124 i need it to be 119 and if the number ends in 6,7,8 or 9 then I need to set the number to round down to 5, so if the number is 1287 i need it to be 1285. I can either get one to work but not the other. the cell with the answer needs a formula for both cases as the column lists are vast and all change at different times.

I hope to hear from someone soon.
Kind regards
Gary

 

[Momentman]

If you have individual expressions that work for both cases, then why not just use an IF statement to couple both together

=IF(RIGHT(A1,1)+0<=4,"Do this", "Do the other")

 

[Sulprobil]

Mappe1
	A	B
1	124	119
Tabelle1

Cell Formulas
Range	Formula
B1	B1	=FLOOR(A1,5)-(MOD(A1,10)<5)

 

[Gary in Guernsey]

Mappe1
	A	B
1	124	119
Tabelle1

Cell Formulas
Range	Formula
B1	B1	=FLOOR(A1,5)-(MOD(A1,10)<5)

Brilliant, thankyou very much