ExcelBanter - formula's

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - formula's (https://www.excelbanter.com/excel-discussion-misc-queries/91048-formulas.html)

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?

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

Here is an example formula.

=ROUND(RAND()*10,0)

--
KellTainer
------------------------------------------------------------------------
KellTainer's Profile: http://www.excelforum.com/member.php...o&userid=34322
View this thread: http://www.excelforum.com/showthread...hreadid=546371

=ROUNDUP(RAND()*100,0)

--
HTH

Bob Phillips

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

"MadisonNYC" wrote in message
...
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?

=ROUNDUP(RAND()*100,0)
never returns 0

=ROUND(RAND()*100,0)
does!

Cheers,
--
AP

"Bob Phillips" a écrit dans le message de news:
...
=ROUNDUP(RAND()*100,0)

--
HTH

Bob Phillips

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

"MadisonNYC" wrote in message
...
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?

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)

"Ardus Petus" wrote in message
...
=ROUNDUP(RAND()*100,0)
never returns 0

=ROUND(RAND()*100,0)
does!

Cheers,
--
AP

"Bob Phillips" a écrit dans le message de news:
...
=ROUNDUP(RAND()*100,0)

--
HTH

Bob Phillips

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

"MadisonNYC" wrote in message
...
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?

"MadisonNYC" wrote:
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?

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.

wow, THANK YOU GUYS SOOOOOOOOO MUCH!!!!!!!!!!!!!

"MadisonNYC" wrote:

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?