Rounding up

  • Thread starter Thread starter Rhiannon
  • Start date Start date
R

Rhiannon

I would like to be able to round up a field as follows :

User enters any value from 0.1 upwards, and this value is displayed i
the field, but for use in a calculation, this value (regardless of wha
it is) is rounded UP to the nearest 5.0.

The best I can find within Excel itself is MROUND, but this only take
effect from a value of e.g. 2.5. Any suggestions
 
You could use =ceiling(a1,5)
in the calculation cells.

(maybe even just put a helper column with that formula in it and have your
calculation cells refer to that adjacent cell.)
 
Function MyRound(Number As Double, Multiple As Double) As Double

'Set up variables
Dim dblDivided As Double
Dim dblIntDivided As Double

'Divide
dblDivided = Number / Multiple
'Integerise
dblIntDivided = Int(dblDivided)

'Round dblDivided to nearest whole number in intDivided
'If the numbers are NOT equal, add one
If dblIntDivided <> dblDivided Then
dblIntDivided = dblIntDivided + 1 'MyRound(dblDivided -
dblIntDivided, 0)
End If

'Return result, returning to nearest multiple
MyRound = dblIntDivided * Multiple

End Function
 
Also, try =int((input+4.9)/5)*5

For example, if the input was 7.6: 7.6+4.9=12.5;
12.5/5=2.5; INT(2.5)=2; 2*5=10.

INT drops anything after the decimal.
 
Back
Top