formula's

M

MadisonNYC

I have a formula giving me random numbers from 1-100 I am trying to round the
range of numbers up to the nearest whole number. Can anyone tell me how to
add a formula to cells that already have formulas in them?
 
K

KellTainer

Hi, just encapsulate your random formula with the ROUND function, whic
takes in two arguments, a value you want to round and the significanc
of the rounding. Since you want a whole number, just set it to 0.

Here is an example formula.

=ROUND(RAND()*10,0
 
B

Bob Phillips

=ROUNDUP(RAND()*100,0)

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)
 
B

Bob Phillips

He wanted theme rounded up between 1 and 100, hence he doesn't want 0.

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)
 
A

Ardus Petus

Misread the OP. You're right!

--
AP

Bob Phillips said:
He wanted theme rounded up between 1 and 100, hence he doesn't want 0.

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)
Click to expand...
 
J

joeu2004

MadisonNYC said:
I have a formula giving me random numbers from 1-100
I am trying to round the range of numbers up to the nearest
whole number. Can anyone tell me how to add a formula
to cells that already have formulas in them?
Click to expand...

I presume you mean that you have a formula like 1+99*RAND(),
and you are asking for an automated way to encapsulate the
formula within ROUND(...,0).

First, let me say that the more conventional way to do this is
use the formula 1+INT(100*RAND()). In general, if you want
random integers between A and B, use A+INT((B-A+1)*RAND())
-- or use RANDBETWEEN, an add-in.

But if you already have a formula and you simply want an
automated way of "adding a formula", the following macro might
suit your needs.

Sub addit()
Dim cell As Range
For Each cell In Selection
If Left(cell.Formula, 1) = "=" Then
cell.Formula = "=round(" & _
Right(cell.Formula, Len(cell.Formula) - 1) & ",0)"
End If
Next
End Sub

To use the macro:

1. Define the macro by typing alt-F11 and clicking on Insert >
Module. Then type or carefully cut-and-paste the above macro.

2. In the spreadsheet, select the range of cells that has the
RAND() formula. Then type alt-F11, be sure the cursor is in
the above macro, and type F5 to execute the macro.
 
Top