formula's

[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?

[KellTainer]

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)

[Bob Phillips]

=ROUNDUP(RAND()*100,0)

--
HTH

Bob Phillips

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

"MadisonNYC" <[email protected]> wrote in message
news:[email protected]...
> 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?

[Ardus Petus]

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

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

Cheers,
--
AP

"Bob Phillips" <[email protected]> a écrit dans le message de news:
[email protected]...
> =ROUNDUP(RAND()*100,0)
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with googlemail if mailing direct)
>
> "MadisonNYC" <[email protected]> wrote in message
> news:[email protected]...
>> 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?
>
>

[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)

"Ardus Petus" <[email protected]> wrote in message
news:[email protected]...
> =ROUNDUP(RAND()*100,0)
> never returns 0
>
> =ROUND(RAND()*100,0)
> does!
>
> Cheers,
> --
> AP
>
> "Bob Phillips" <[email protected]> a écrit dans le message de news:
> [email protected]...
> > =ROUNDUP(RAND()*100,0)
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with googlemail if mailing direct)
> >
> > "MadisonNYC" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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?
> >
> >
>
>

[Ardus Petus]

Misread the OP. You're right!

--
AP

"Bob Phillips" <[email protected]> a écrit dans le message de news:
[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> =ROUNDUP(RAND()*100,0)
>> never returns 0
>>
>> =ROUND(RAND()*100,0)
>> does!
>>
>> Cheers,
>> --
>> AP
>>
>> "Bob Phillips" <[email protected]> a écrit dans le message de news:
>> [email protected]...
>> > =ROUNDUP(RAND()*100,0)
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > (replace somewhere in email address with googlemail if mailing direct)
>> >
>> > "MadisonNYC" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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?
>> >
>> >
>>
>>
>
>

[[email protected]]

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

[MadisonNYC]

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?