Unusual problem with Excel - Need some help

Ron Rompen

Trusted Information Resource
#1
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.
 
Elsmar Forum Sponsor
T

Tara Monson

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

I'd love to give it a shot!
 

Ron Rompen

Trusted Information Resource
#3
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.
 
W

Wilderness Woody

#5
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.
 

David-D

Involved In Discussions
#7
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
 
Thread starter Similar threads Forum Replies Date
Ron Rompen Shiftwork Scheduling - An unusual problem - Need help please Human Factors and Ergonomics in Engineering 13
gohyl Printed Circuit Board - Unusual sighting between pads, is it normal. Manufacturing and Related Processes 13
L Unusual requests from some auditees? bosses Internal Auditing 8
J Capability Analysis - Unusual Statistical Distribution of my Proccess Capability, Accuracy and Stability - Processes, Machines, etc. 5
S Have you seen and What is an Unusual Controlled Document Records and Data - Quality, Legal and Other Evidence 6
Chennaiite "Unusual Situation" in Gemba? Just Food for thought Quality Manager and Management Related Issues 0
Ron Rompen Unusual GD&T Callout similar to the MMC or LMC symbol Inspection, Prints (Drawings), Testing, Sampling and Related Topics 4
D Nissan asks about an Unusual Situations procedure IATF 16949 - Automotive Quality Systems Standard 5
B Service and maintenance company - I have an unusual exclusion to talk about in 7.4 ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 23
P QMS Process Map - Comments on my unusual QMS process map Process Maps, Process Mapping and Turtle Diagrams 5
M Part Time Quality - Am I the only one? Is Part Time Quality unusual? ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 11
A Unusual ('Weird') Standards -'Bouncers' get their own standard Various Other Specifications, Standards, and related Requirements 5
Beth Savage Problem switching DNS Elsmar Cove Forum Suggestions, Complaints, Problems and Bug Reports 0
M Help on formulating a LP minimization problem Manufacturing and Related Processes 2
O Problem in Minitab - ERROR * Factor Voltage is highly correlated with other terms Using Minitab Software 10
J Mandatory Problem Reporting and Recalls Canada Medical Device Regulations 0
J How to keep MDD certificate valid when legal manufacturer has liquidity problem EU Medical Device Regulations 0
normhowe "The Problem with Quality Management: Process orientation, controllability and zero-defect processes as modern myths" Book, Video, Blog and Web Site Reviews and Recommendations 2
M Problem with nonlinear regression Using Minitab Software 12
M Problem Solving in Fiat Automobiles Problem Solving, Root Cause Fault and Failure Analysis 0
R Problem solving activity - Three hours to fix the issue Manufacturing and Related Processes 15
T Formal Q Self Assessment - Problem with assigning Product and Process Customer and Company Specific Requirements 1
NDesouza Go See, Think, Do (GSTD) Problem Solving Activity Food Safety - ISO 22000, HACCP (21 CFR 120) 8
Marc Medical device vulnerability highlights problem of third-party code in IoT devices Other Medical Device and Orthopedic Related Topics 1
P How far an operator can reach into a machine before it becomes an ergonomic problem CE Marking (Conformité Européene) / CB Scheme 5
V Ammeter calibration - Measuring head (on pic.) problem General Measurement Device and Calibration Topics 1
G Problem Resolution Report Monitoring - Customer complaint or PRR as general motors use Customer Complaints 12
S Relationship between IEC 62304 problem resolution and ISO 13485 IEC 62304 - Medical Device Software Life Cycle Processes 8
qualprod Add new action plans in CA, while waiting effectiveness - Same problem reappears ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 13
bryan willemot Documenting past problem history for fasteners for AS9100 Rev D AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 2
Marc Problem Opening Attachments Elsmar Cove Forum Suggestions, Complaints, Problems and Bug Reports 4
Marc Problem with 21 CFR Part 820 - US FDA Quality System Regulations (QSR) sub-forum link - 2 May 2019 Elsmar Cove Forum Suggestions, Complaints, Problems and Bug Reports 1
K Does anyone have a copy of a GM 5 Phase Problem solving form Problem Solving, Root Cause Fault and Failure Analysis 1
D Training for AS13000 - Problem Solving Requirements for Suppliers Training - Internal, External, Online and Distance Learning 17
eule del ayre Manual Inventory System - Problem is discipline of the employees Lean in Manufacturing and Service Industries 7
G When preventative action is prohibited by cost in 8D problem solving Problem Solving, Root Cause Fault and Failure Analysis 1
G When preventative action is prohibited by cost in 8D problem solving Problem Solving, Root Cause Fault and Failure Analysis 8
Marc vBulletin to Xenforo Import Problem Reports 28180928 Elsmar Cove Forum Suggestions, Complaints, Problems and Bug Reports 25
M Training in 8D Problem Solving as a Preventive Action? Problem Solving, Root Cause Fault and Failure Analysis 9
A 5Why vs. 8D - Problem Solving Problem Solving, Root Cause Fault and Failure Analysis 19
K Documented problem solving and documented error-proofing - IATF 16949 10.2.3 & 10.2.4 Internal Auditing 7
V Query on PA66+GF15% - Color variation (Natural to Yellowish) problem Manufacturing and Related Processes 20
J Can someone help me get the UCL AND LCL for this problem? Statistical Analysis Tools, Techniques and SPC 2
P Problem with IATF 16949 Clause 7.2.3 Requirements (Internal Auditor Competency) IATF 16949 - Automotive Quality Systems Standard 3
K Problem with Cable Composed of Coextruded Litz Wire and Tube Manufacturing and Related Processes 1
W Documented Problem-Solving for Automotive IATF 16949 - Automotive Quality Systems Standard 4
R Review of "Key Data" for contract labs, but SOP doesn't define "key data". Problem? Pharmaceuticals (21 CFR Part 210, 21 CFR Part 211 and related Regulations) 2
M Taguchi Loss Function Problem Misc. Quality Assurance and Business Systems Related Topics 2
R RoHS Compliance Problem with One Component RoHS, REACH, ELV, IMDS and Restricted Substances 5
Q Problem Solving Techniques - 5 why or Fishbone diagram ? Problem Solving, Root Cause Fault and Failure Analysis 8

Similar threads

Top Bottom