Can someone tell me what the Rand() function in the FormulaBuilder is meant to do? A user discovered that when he uses the function it inevitably brings down the application. It appears that it calculates a decimal between 0 and 1. I would think it would do this once per row. However, it calculates each row over and over constantly regenerating which takes up 50% of the cpu and eventually freezes up the app from accepting input.
The RandBetween appears to regenerate each row's value a few times before eventually staying static and not hosing the application.
Thanks in advance.
The Rand function generates a new value any time the function is recalculated for any reason.
The CalcManager is optimized so that it only recalculates a function when something has changed. So if you are using Rand in a cell formula, then some other value reference in that formula must be changing if things are being recalculated continuously.
Any good way to verify what value reference is changing? I literally just add the formula and sit back and watch it continually recalculate without interacting with the application at all. Is there any chance that the values in other cells being calculated for the random number could trigger the change?
We provide the formula builder to our users. That paticular user was attempting to use the rand function to be able to pull a random sample of data on the grid. Looks like we will just have to remove the summary on one of our formula columns and enforce that if they use that function that they have to use that one column. Thanks.
Well... if you have a summary that is referencing the column, then any time any value in the column changes, the summary will be dirtied. When the summary is dirties, every cell in the column will have to be recalculated. So that could, in theory, cause a problem, since recalculating each cell would be continuously dirtying the summary which would in turn continuously dirty the cells since they are always considered dirty if that are using Rand.
It's hard to offer you any potential solutions without know what your formulas are doing. Why use Rand ni a column formula?
One way you might be able to get around this is to avoid using the Rand function. Instead, you could use the DotNet framework's Random class to generate a random number and then apply the resulting value to a NamedReference which is used in your formulas. That way, you have control over when the random number is regenerated.
I did verify that there is no other value changing. The formula column does not depend on anything, nor is there any other formula set. I believe I did narrow it down a bit. Per the users the colums need a Summary. So at the bottom of the rows is a Summary of type Sum. Should this impact the rand function and can it be overcome or do I need to trade off and choose between the two? Thanks.
ecarrish said:Is there any chance that the values in other cells being calculated for the random number could trigger the change?
Does the formula that is using the Rand function rely on some other value that is also the result of a formula? If so, then any time that other value is calculated, the Rand function will be recalculated. And if you have another formula that is referencing that one, it could cause a circular reference.
The CalcManager has code to detect circular references, but I'm not sure if it's possible to catch one that is caused by an AlwaysDirty function like Rand.
Hello,
Would it be possible for you to provide a sample project which reproduces the issue? You can attach a file to a post by selecting the Options tab when creating the post.