Rounding up

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
 
D

Dave Peterson

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.)
 
R

Rhiannon

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
 
R

Roy

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.
 
Top