Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula's
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula's
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula's
=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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula's
=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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula's
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula's
"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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula's
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change column letters to correct ones in many formulas automatically? | Links and Linking in Excel | |||
how can i get formulas in excel to copy and paste? | Excel Worksheet Functions | |||
How to make Excel run limited number of formulas on a given worksh | Excel Discussion (Misc queries) | |||
Way to make Excel only run certain formulas on a worksheet? | Excel Discussion (Misc queries) | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |