Unusual problem with Excel - Need some help

[Ron Rompen]

I have a rather unusual problem with Excel that I hope someone can help me with.

I am running Excel 2010, and just spent a considerable amount of time creating a spreadsheet to track reject quantites by part, defect type, etc etc etc. All in all, a fairly nice piece of work.

At the end of my columns, I wanted to SUM all the reject quantites, and used the SUM formula (=sum(C3:K3) to calculate the value.

However, when I tried to copy the formula to rows D thru X, all it copied was the value returned from the first formula, even though when I inspect the cell, it appears to be correct (i.e. =sum*D4:K4, etc etc). Also, when I change any of the values in the rows/columns (to reflect actual data) the value shown in the SUM cell doesnt change.

Anyone know what the problem might be, and how to fix it? I used to think I was pretty good at Excel, but apparantly I was mistaken.

 

[Tara Monson]

Could you attach the file? And specify what you wanted totaled up?

I'd love to give it a shot!

 

[Ron Rompen]

Thanks Tara, but I sent it to one of my co-workers, and of course it works on THEIR computer, as well as working properly when it is sent back to me. But even if I create a very simple sheet (a few columns and rows of numbers) I still have the same problem. It appears that there is something incorrectly setup in my Excel, but I don't know what it is.

 

[t.PoN]

I think this is usually because you have diabled the auto calculation option. in the Formula tab, Calculation Option, tick automatic.

Try it, I hope it works

 

[Wilderness Woody]

Thanks Tara, but I sent it to one of my co-workers, and of course it works on THEIR computer, as well as working properly when it is sent back to me. But even if I create a very simple sheet (a few columns and rows of numbers) I still have the same problem. It appears that there is something incorrectly setup in my Excel, but I don't know what it is.

Check your settings for how your default calculation is set.

Here's information from Excel Help page:
Change when a worksheet or workbook recalculates

As calculation proceeds, you can choose commands or perform actions such as entering numbers or formulas. Excel temporarily interrupts calculation to carry out the other commands or actions and then resumes calculation. The calculation process may take more time if the workbook contains a large number of formulas, or if the worksheets contain data tables (data table: A range of cells that shows the results of substituting different values in one or more formulas. There are two types of data tables: one-input tables and two-input tables.) or functions that automatically recalculate every time the workbook is recalculated. Also, the calculation process may take more time if the worksheets contain links to other worksheets or workbooks. You can control when calculation occurs by changing the calculation process to manual calculation.


Important Changing any of the options affects all open workbooks.

1. Click the File tab, click Options, and then click the Formulas category.
2. Do one of the following:
   • To recalculate all dependent formulas every time you make a change to a value, formula, or name, in the Calculation options section, under Workbook Calculation, click Automatic. This is the default calculation setting.
   • To recalculate all dependent formulas ? except data tables ? every time you make a change to a value, formula, or name, in the Calculation options section, under Workbook Calculation, click Automatic except for data tables.
   • To turn off automatic recalculation and recalculate open workbooks only when you explicitly do so (by pressing F9), in the Calculation options section, under Workbook Calculation, click Manual. Note When you click Manual, Excel automatically selects the Recalculate workbook before saving check box. If saving a workbook takes a long time, clearing the Recalculate workbook before saving check box may improve the save time.
   • To manually recalculate all open worksheets, including data tables, and update all open chart sheets, on the Formulas tab, in the Calculation group, click the Calculate Now button.
     
   • To manually recalculate the active worksheet and any charts and chart sheets linked to this worksheet, on the Formulas tab, in the Calculation group, click the Calculate Sheet button.

Tip Alternatively, you can change many of these options outside of the Excel Options dialog box. On the Formulas tab, in the Calculation group, click Calculation Options, and then click Automatic.
Note If a worksheet contains a formula that is linked to a worksheet that has not been recalculated and you update that link, Excel displays a message stating that the source worksheet is not completely recalculated. To update the link with the current value stored on the source worksheet, even though the value might not be correct, click OK. To cancel updating the link and use the previous value obtained from the source worksheet, click Cancel.

 

[Tara Monson]

I agree with the above tips.

Let us know if you get it to function!

 

[David-D]

I also suspected, based on your description, that you have the auto calculate turned off. I will usually only turn it off for really big spreadsheets with lots of embedded formulas (like 10 MB Monte Carlo simulations with lots and lots of random functions).

It is useful to prevent excel from bogging down and also useful if you don't want it to recalculate until you're ready at which point hit F9.

David

 

[Ron Rompen]

Thanks guys, that incorrect setting of the auto-calculate was indeed the problem. Many thanks to all of you.