Fixed Random Function

Hi,

I have a template in which I'm using a random function to generate (or make up) hours for various projects worked on during specific start & finish dates. Within these projects, I also have 5 primary sub-tasks with handful of secondary tasks.


The function I'm using is:

=RAND()*$C$6*100

$C$6 is pointing to a specified percentage, in this instance 12%


The problem with my worksheet is now when I edit other areas, my table is randomizing.

Is there a way to "fix" or 'lock" the randomization, once I have desirable numbers in place? If so, how?

Thanks

You'd need to copy the formulas and paste as values.

Hi,

I attached an Excel 2007 file for questioning.

When reviewing the file, you will see Colums I - R, Rows 27 - 39. In the task areas, I have bunch of random numbers that were generated Estimated Min. Max. hours found in Column D & E & its respective rows.

This section seems to work well.

Now, I'm questioning when I edit other areas, such as items found above Row 26, my numbers are being re-randomized.

Why is this?
More importantly, how can randomize desired sections - Below Row 26 & then lock it, so it does not modify other calculations?

Thanks

Hi e4excel,

I had a feeling I would have to do this. I was hoping to avoid this because I'm still creating the worksheet & wanted to minimize having similar worksheets.

I've seen this done before, but not familiar on how to do this. I've seen some worksheets that has a macro icon to "copy" data from other worksheets.

If I were to do something like this, will the "copied" information come over as values or as the Random formula being used?

Also, how do I create a "Copy" macro to get desirable results - In this case, just the estimated randomized hours values to its appropriate cells? I have 5 primary Task sessions with a handful of sub-tasks.

Thanks

Hi e4excel,

Thank you very much for your assistance & the file. I will be checking it out shortly.

Hi e4excel,

I finally had a chance to check out your file. Looks like you created a copy function that removes the formulas.

I suppose this works. After working with my file some more, it appears that some of these randomness is excessively exceeding my project durations. Now, I need to figure out on how to resolve this issue.

Thanks for the help.