Solved

Excell 2000 - Cell calculations do not function correctly

Posted on 2001-08-10
18
1,811 Views
Last Modified: 2010-05-19
I haven't the time to research this one, therefore I need help from the experts.

Creating a worksheet that calculates values both across as well as down. As an example:

| quantity | value | 1.15 | sum |
| quantity | value | 1.15 | sum |
| quantity | value | 1.15 | sum |
| quantity | value | 1.15 | sum |
| quantity | value | 1.15 | sum |
|          |       |      |total|

The value of "sum" is cell 1*cell 2*cell 3

Initially sum calculates correctly, however if I change either the quantity of value figures, sum does not recalculate (or update).

What needs to be changed?

Dennis
0
Comment
Question by:dew_associates
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 5
  • +1
18 Comments
 
LVL 17

Expert Comment

by:calacuccia
ID: 6371860
Hi Dennis,

It seems like you have calculation set to manual.

Go to Tools/Options and on the tab 'Calculation', check the option 'Automatic'.

You can also hit F9 to force a manual recalculation, but if you set it to Automatic as described above, it should work.

calacuccia
0
 
LVL 25

Author Comment

by:dew_associates
ID: 6371895
That's what I thought it was originally Cal, but no go, it's set to auto.

0
 
LVL 17

Expert Comment

by:calacuccia
ID: 6371904
I was already surprised by the person asking this kind of question ;-)

Can you mail me the workbook?

calacuccia@hotmail.com

What happens if you hit CTRL+ALT+F9 ?
0
Increase your protection from Zero Day threats!

Running two Antivirus' is never a good idea.
Taking advantage of Multiple Security layers on the other hand can often save your hide.
See which top notch security software brands have been proven to happily coexist together.
Reduce your chances of becoming a statistic.

 
LVL 25

Author Comment

by:dew_associates
ID: 6371926
Well Cal, there are those times that output takes priority over why something doesn't work as it should. If I can, I'll strip out the proprietary stuff and send it to you.
0
 
LVL 16

Expert Comment

by:GUEEN
ID: 6371927
Do you have the cell values as number and not text values (hey I know this is a long shot here - but . . .)
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 6371942
Have you tried the CTRL+ALT+F9 thing?

Usually, this solves all calculation problems or bugs (and they exist, see http://support.microsoft.com/support/kb/articles/Q171/3/39.ASP)

0
 
LVL 25

Author Comment

by:dew_associates
ID: 6371967
Cal, CTRL+ALT+F9 does update the sum.

Shekerra, the values in the cells are set to numeric.

Cal, the article really doesn't apply here as this is Office 2000 with all of the latest updates and fixes.
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 6372001
I'm not surprised the article does not apply, it was more meaned as an example, there are some others I know of:

When Using 'Natural Language Formulas'
http://support.microsoft.com/support/kb/articles/Q200/6/88.ASP

This article shows a method to programmatically force Excel to recalculate, using the Application.CalculateFull method.

Is your problem (the urgent one) solved now?
0
 
LVL 6

Expert Comment

by:Triskelion
ID: 6374351
Were all of the values typed in or pasted from something else?
If you retype all (ALL) of the values (ALL), will it recalc?

If that does nothing...
Can you copy the contents and paste them into a fresh sheet?
0
 
LVL 16

Expert Comment

by:GUEEN
ID: 6374549
Hmmmm do you have Office 2000 patched?
Perhaps you have an addin that turns calculation off?

How about putting this statement at the top of VBA function:
Application.Volatile (True)

Per chance are you using autofilter?






0
 
LVL 16

Expert Comment

by:GUEEN
ID: 6374572
Private Sub Worksheet_Activate()
ActiveSheet.Calculate
End Sub
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 6374629
shekerra,

Application.Volatile (True) is a dangerous thing to do, especially when working with big workbooks.

It means that ALL functions are recalculated whenever any change is made to the workbook. This means slow performance. I would not use this as long as not needed absolutely.
0
 
LVL 16

Expert Comment

by:GUEEN
ID: 6374642
OK Cal agreed - I wasn't aware that this was a big WB.
0
 
LVL 16

Accepted Solution

by:
GUEEN earned 200 total points
ID: 6374650
PSS ID Number: Q215732
Article last modified on 11-29-2000
 
SYMPTOMS
========
 
When you create a formula in Microsoft Excel that uses the SUMIF() worksheet function, and you then change one of the values that you want to sum, the formula does not return an updated sum as expected.
 
CAUSE
=====
 
This issue can occur if the range that is evaluated for the criteria and the sum_range are not the same length, and the value that you change is outside of the range of the sum_range but inside the range of the cells being evaluated, as in the following example:
 
1. Start Excel, and then create the following worksheet.
 
   A1: 3  B1: 4
  A2: 3  B2: 5
  A3: 3  B3: 6
  A4: 3
 
2. In cell C4, type the following formula:
 
   =SUMIF(A1:A3, A4, B1:B2)
 
   With this formula, you evaluate the range of cells A1:A3 using the criteria in   cell A4. The range of cells that you want to sum is specified as B1:B2, which
  is a different length than the range of cells that you are evaluating. Excel   automatically extends the sum_range to include B3 and returns a value of 15.
 
3. Change the value in cell B3 to 7, and then press F9 to recalculate.
 
B3 is not specified in the formula, therefore the recalculate command doesn't
work as expected.
 
RESOLUTION
==========
 
To resolve this issue, change the formula so that the criteria range and the sum_range are the same length.
 
In the example shown in the "Cause" section, change the formula to read as
follows:
 
   =SUMIF(A1:A3, A4, B1:B3)
 
   -or-
 
   =SUMIF(A1:A2, A4, B1:B2)
 
WORKAROUND
==========
 
To work around this issue, change or delete and then re-enter a value in the specified sum_range.
 
In the example shown in the "Cause" section, after performing step 3, delete the value in B1, and then re-enter the value 4. The formula recalculates and then
returns the correct value of 16.
 
STATUS
======
 
Microsoft has confirmed this to be a problem in the Microsoft products that are
listed at the beginning of this article.
 
REFERENCES
==========
 
For more information about the SUMIF() worksheet function, click Microsoft Excel
2000 Help on the Help menu, type "sumif" (without the quotation marks) in the
Office Assistant or the Answer Wizard, and then click Search to view the topic.
 
0
 
LVL 25

Author Comment

by:dew_associates
ID: 6374674
Hmmm, okay group, gimme time to go through some of this.
0
 
LVL 25

Author Comment

by:dew_associates
ID: 6376834
Sure, it had to be the last thing I checked on this list. Thanks gang.
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 6376950
Well found, shekerra. I did not see this link in my queeste.
0
 
LVL 16

Expert Comment

by:GUEEN
ID: 6377258
Cal - I found it on technet :)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Office 365 adding a domain 3 35
Excel Index/Match issue 4 15
Day Count issue - Days360? 12 26
How to turn this IF statement into a UDF? 5 23
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question