Really weird Excel problem

[Kermit power]

Hi all,

I'm hoping that as always, the power of PH will utterly overwhelm the power of puny specialist Excel forums and deliver a quick and accurate answer to my conundrum!

For at least 25 years I have, when applying a formula to multiple rows, created it in the top cell and then just dragged down the column in question to replicate the formula all the way down. Oddly, this now seems to have stopped working!!!

With my current example, I just want an incredibly simple formula to show how far under or over their contracted volume is likely to be at the end of their contract. In column A I have their average monthly consumption to date. In Column B I have their monthly contracted volume. In Column C, I have a formula of =(A1/B1). With column C set as a percentage, this predicts over-consumption with a figure in excess of 100% under under-consumption with a figure below 100%.

Simple, right?

Except now, when I drag C1 down the rest of column C, although the formulae in the subsequent rows will successfully change to be =(A2/B2)....(An/Bn), the result it displays stays as the result shown in C1 until I go into the formulae in lower cells. I don't need to change anything to get them to actually run the calculation, just click into it in the formula bar and then click away to a different cell...

Help!!! I have no idea what is going on???

[Kermit power]

Just to add, I thought I'd close out Excel and relaunch it to see if things went back to normal, and I'm now getting a warning box saying "Calculation is incomplete. Recalculate before saving?".

[kiethton]

if the cells its linking to are ok for the new calculation press F9 - looks like you have auto-calculation turned off

[Planet Claire]

Excel works ok for me so just wondering if the automatic calculation has been turned off - Options -> Formulas?

What version are you running?

[Kermit power]

kiethton said:

if the cells its linking to are ok for the new calculation press F9 - looks like you have auto-calculation turned off

Yes, that turns out to be precisely what it was. I haven't the faintest notion how it managed to get turned off, but now turned back on and all working, thanks.

Next question... Under what circumstances would you NOT want formulae to auto-calculate? I assume there must be one?

[hyphen]

Kermit power said:

Yes, that turns out to be precisely what it was. I haven't the faintest notion how it managed to get turned off, but now turned back on and all working, thanks.

Next question... Under what circumstances would you NOT want formulae to auto-calculate? I assume there must be one?

When you are making lots of changes and only need to calculate at the end of them.

For complex spreadsheets with lots going on, auto calc can really slow down performance. Better to switch to manual, make the changes and then calculate.

[kiethton]

Kermit power said:

kiethton said:

if the cells its linking to are ok for the new calculation press F9 - looks like you have auto-calculation turned off

Yes, that turns out to be precisely what it was. I haven't the faintest notion how it managed to get turned off, but now turned back on and all working, thanks.

Next question... Under what circumstances would you NOT want formulae to auto-calculate? I assume there must be one?

Data tables or when using/building a very large, inter-linked sheet - it can make the machine unuseable/laggy

[K12beano]

Kermit power said:

Excel

and

Kermit power said:

Simple, right?

You seemed like a rational human being until you made this, presumably rhetorical, association. My sympathies.

No, I cannot help, but anything "simple" in Excel just doesn't occur unless you start again from scratch. Almost guaranteed!

[Kermit power]

K12beano said:

Kermit power said:

Excel

and

Kermit power said:

Simple, right?

You seemed like a rational human being until you made this, presumably rhetorical, association. My sympathies.

No, I cannot help, but anything "simple" in Excel just doesn't occur unless you start again from scratch. Almost guaranteed!

It's a good thing you included the word "almost", or you'd be paying out on your guarantee!!! I had somehow managed to turn off auto-calculation, so once I knew that, it was dead simple to resolve.

[K12beano]

Ah - good that it was the easier end of the spectrum!