Different Formula, Same Result

This one comes up all the time, and I thought it was worth a post. You enter a formula and it calculates correctly. Then you fill or copy the formula and you get the same answer, but you know the answer should be different.

ManCalc1

Nine times out of 10, the problem is that calculation is set to manual. Take a look at the lower left of your spreadsheet.

ManCalc2

If it says “Calculate”, then calculation is set to manual and it needs to be automatic if you expect the formulas to change. Go to Tools > Options > Calculation and select the Automatic option button.

ManCalc3

Posted in Uncategorized

4 thoughts on “Different Formula, Same Result

  1. Dick…one of the guys on the VBAExpress board helped me with this macro to set up a MANUAL/AUTOMATIC calculation button. Just create a new toolbar called “Calcs” and set up a button with this macro:

    Sub ManAuto()
    With Application
    If .Calculation = xlCalculationManual Then
    .Calculation = xlCalculationAutomatic
    Set myControl1 = CommandBars(“Calcs”).Controls(1)
    myControl1.State = msoButtonUp
    Else
    .Calculation = xlCalculationManual
    Set myControl1 = CommandBars(“Calcs”).Controls(1)
    myControl1.State = msoButtonDown
    End If
    End With
    End Sub

  2. I have fun with this little problem. I use it at work many times. It is a very helpful tool. You just need to be aware of what mode you’re in. We have several people at my work that chastise me for my Excel skills. They consider themselves experts. Well, I leave the spreadsheet in Manual calc and pass it off to these so called Excel experts. Then, I really get an ear full about the spreadsheet not working. “Here you go, genius, press the button.”

  3. Or, you can press F9 to recalculate everything that needs calculating, or press shift-F9 to just recalculate the current sheet.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.