Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Random number function; stop Excel re-calculating all cells

0

I am trying to simulate a flow meter error function. For example, the meter that puts petrol or diesel in your car.

Such meters have errors thus if the actual amount dispensed is 100 litres and the meter is just (+/-)2.0% accurate, the registered quantity can vary from 98.0 to 102.0 litres. Over a number of transactions the error in the cumulative amount should reduce.

I have created a table in which in the C column i enter the true amount delivered beginning with row 5. Hence the first transaction will be entered into cell C5.

In the second column I use Rand to calculate to registered quantity using the Rand Function.

The code in D5 is thus:

=IF(C5=0,0,(RANDBETWEEN(980,1020)/10)*(C5/100))

This will return a value between 98.0% of the true consumption and 102.% of the true consumption. e.g. the registered quantity is 99.4 litres. This does what I want for that cell.

In the real world that result is now fixed. That transaction cannot be rescinded.

However, when I move on to the next row and enter a new value for the next transaction, (C6), not only does it calculate registered value in the second column (D6) for that transaction, it also recalculates the registered quantity in the previous row (D5).

What was 99.4 litres might now become 101.7 litres.

This I wish to stop.

Once i have made an entry in C5 coulmn and a calculated random number generated result appears in  (D5), i do not want it to change when I enter new transaction in the following rows.

Is there any way I can do this?

Answer
Discuss

Answers

0
Selected Answer

Hi JMW

You can stop the random number changing by changing Excel's default calculation mode (from Automatic) to Manual.

To do this, in the ribbon go to Formulas and then the section Calculation. Click on the down arrow of "Calculation options" then select Manual (you could also do this via File, Options/ Formulas then "Calculation options" where you can also disable recalculation on closing the workbook).

That will stop the recalculation when you move between rows.

To change a single random number, go to edit that cell's formula and press Return.

When you want all the randon numbers to change, you can force the sheet (or workbook) to recaclulate via F9 or the buttons under that Calculation area. 

When you quit your workbook, it might be best to return Calculation option to Automatic (for other workbooks). In a macro-enabled workbook, that could be done automatically in VBA by the WorkbookOpen and WorkbookClose events.

Hope this works for you.

Discuss

Discussion

Hi,
thanks for that and it works for me.
I now need to make it work for others without letting them change the formulae.... maybe a macro? But that is good enough for my own purposes at this time
Thanks very much for the prompt helpful answer.
JMW123 (rep: 2) Dec 12, '20 at 3:54 pm
JMW

To prevent people changing (or even seeing) your formula, you could copy/paste special (text) or see my recent forum suggestion on the question of Hiding your formula.
Suggest you ask a fresh question on preventing automatic  calculation for others (which a search by other forum users wouldn't find under this subject)
John_Ru (rep: 6102) Dec 13, '20 at 3:00 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login