Excel rand function creating errors

  Terry Brown 19:53 20 Aug 2013
Locked
Answered

I come across a problem, which I cannot see any way to cure, can you help? I was creating an expenses workbook for traveling sales-persons, where the completed expense sheets would be forwarded to the office (via email) and the weekly totals would be added to a master sheet called expenses.

Making a dummy sheet (for data test) I put in the first line of figures and used rand() to generate the rest, and found this fault.

In reality every figure will be input manually, so the fault does not arise, but what causes the rand function to change values when data in an unrelated cell is typed in.

Try this as a test.

Open a blank Excel page (any version) in any cell type in =rand() now type a character (letter or number) or word into any other cell on that worksheet and the random number changes. Any idea why?, and is there any way to stop it?

Terry

  Woolwell 20:01 20 Aug 2013
Answer

MS Excel help should explain it. The number automatically changes when the worksheet recalculates unless when entering the formula you press F9 instead of enter on the formula line.

  Woolwell 18:18 21 Aug 2013

Problem solved?

  Terry Brown 12:32 22 Aug 2013

Sorry for the delay- out all day yesterday.

I have now found the answer from an friend who works in a office as an accountant, and he did not know about it until he experimented.

The F9 function turns on the automatic calculation, however to disable it you have to go to to :

File; Options; formula's ;Change from Automatic to Manual.

If you was working on someone else's spreadsheet and did not not know the Rand or Randbetween funcion had been used it could cause serious errors.

Thanks for replying

Terry

  Woolwell 15:04 22 Aug 2013

Sorry but F9 does not turn on automatic calculation. It is a shortcut key to calculate the worksheet or a portion of it or in the case I was using it change the formula to a random number.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?