Round up

[Mike B]

How do I get a number in a cell rounded up without having to perform the
ROUNDUP function in another cell.

Mike

 

[Gary''s Student]

Use a macro

This routine, for example, will roundup user selected cells to the next
higher integer value:

Sub roundupp()
Dim r As Range
For Each r In Selection
With r
v = Int(.Value) + 1
.Value = v
End With
Next
End Sub

 

[joeu2004]

How do I get a number in a cell rounded up without
having to perform the ROUNDUP function in another cell.

Your question is unclear. Do you mean, for example, that if you have
the value 12.4 in A1 and B1 has the formula =A1+A2, you would like B1
to use the value 13 for A1?

That is what "without having to perform the ROUNDUP function in
__another__ cell" means to me.

Other than ensuring that A1 itself is rounded up, I don't believe that
is possible.

Moreover, I hasten to point out that no matter how we might interpret
your inquiry, you neglect to say to what level of precision things
should be rounded up automatically. So I think it is safe to say that
there is no way to do what you ask, in general.

You could set the Precision As Displayed option under Tools > Options

Calculation. Then, by formatting A1 as Number with 0 dp, the value

of A1, not just the displayed number, will be rounded (but not
necessarily rounded __up__); so all references to A1 will use the
rounded value.

But besides the fact that that rounds, not rounds up as you requested,
Precision As Displayed might have unexpected results if you are not
careful. It has a pervasive effect on the entire workbook.

 

[joeu2004]

PS....

You could set the Precision As Displayed option under
Tools > Options> Calculation.
[....]
But besides the fact that that rounds, not rounds up
as you requested, Precision As Displayed might have
unexpected results if you are not careful.

I might also point out that Precision As Displayed means "Precision of
the Result as It Is Displayed (Formatted)". It does not mean that the
operands are converted to the precision of the cell in which the
calculation is performed or even that the calculation (i.e., each
subexpression) is performed with that precision.

For example, if A1 displays 12.5, and A2 displays 2.5, and B1 has the
formula =A1*A2 formatted as Number with 0 dp, the result in B1 is 31,
not 39.

 

[Mike B]

Your question is unclear. Do you mean, for example, that if you have
the value 12.4 in A1 and B1 has the formula =A1+A2, you would like B1
to use the value 13 for A1?

That is what "without having to perform the ROUNDUP function in
__another__ cell" means to me.

Other than ensuring that A1 itself is rounded up, I don't believe that
is possible.

Moreover, I hasten to point out that no matter how we might interpret
your inquiry, you neglect to say to what level of precision things
should be rounded up automatically. So I think it is safe to say that
there is no way to do what you ask, in general.

You could set the Precision As Displayed option under Tools > Options
of A1, not just the displayed number, will be rounded (but not
necessarily rounded __up__); so all references to A1 will use the
rounded value.

But besides the fact that that rounds, not rounds up as you requested,
Precision As Displayed might have unexpected results if you are not
careful. It has a pervasive effect on the entire workbook.

Thanks for your help, appolgies for not being more specifi, however your
reply has answered another problem I encountered

Mike