Rounding

Gary in Guernsey

New Member
Joined
Mar 10, 2014
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,059
Office Version
  1. 365
Platform
  1. Windows
If you have individual expressions that work for both cases, then why not just use an IF statement to couple both together
Code:
=IF(RIGHT(A1,1)+0<=4,"Do this", "Do the other")
 

Sulprobil

Board Regular
Joined
May 12, 2020
Messages
117
Office Version
  1. 2016
Platform
  1. Windows
Mappe1
AB
1124119
Tabelle1
Cell Formulas
RangeFormula
B1B1=FLOOR(A1,5)-(MOD(A1,10)<5)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,193
Messages
5,546,481
Members
410,742
Latest member
WalterSil
Top