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?