# Solved: EXCEL 97 Not Calculating

Discussion in 'Business Applications' started by Warne57032, Feb 12, 2007.

Not open for further replies.

Joined:
Feb 12, 2007
Messages:
35
Hello all...I've been having calculation issues in excel 97. Let me start by letting you know that the "Automatic Calculation" feature is engaged. Here's my problem:

I have a spreadsheet that contains four tables of formulas: 1)Deficiencies by Material Weakness 2)Deficiencies by Business Process 3) Total Deficiencies and 4)Aging of Deficiencies. The problem with this is that the sum of the Def. by MW and Def. by BP do not equal and neither of the totals concur with the Total Deficiencies.

Other issue happening, I've entered a formula to calculate the aging of outstanding items. When I enter the formulas, it calculates and displays the totals as I desire. I save the workbook, go into another workbook and do some work, but when I come back to the first workbook, the calculations are all "0" and aren't calculating.

2. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
can you post an example spreadsheet at all ?

Joined:
Feb 12, 2007
Messages:
35
Wish I could...sensitive data though. Here's an idea of what I'm using to calculate the aging and sum up the categories.

Using this formula to get the aging between Estimated Implementation Date and current day: =I4-TODAY()

Using this formula to define the aging category:

=IF(W4<-60,">60 Days",IF(W4<-30,"31-60 Days",IF(W4<0,"1-30 Days",IF(W4>0,"Not Due"))))

And using this formula to sum the aging categories:

COUNTIF(X4:X24,"Not Due")

You can just open excel and enter 10 random dates in column A. Enter the first formula I gave you in column B (referencing column A) and the second formula in column C (referencing column B). Once you get those into place, then set the last formula in column D (referencing column C).

4. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
seems to work for me -
you need to cover the situation for = 0

heres the book
see how it works on your PC

#### Attached Files:

• ###### agung.xls
File size:
14 KB
Views:
173

Joined:
Feb 12, 2007
Messages:
35
Yeah....its the weirdest thing...it seems to calculate just fine when that's the only thing that's in the spreadsheet. I'm not sure if its not working because I have too many other formulas going on or not.

6. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
i have seen a problem when theres 1000's of calculations - but that was on an old 133 PC

would it take a long while to rebuild the spreadsheet

Joined:
Feb 12, 2007
Messages:
35
ETAF,

I'm gonna work on creating a dummy spreadsheet that can better illustrate what's happening on this end. Our helpdesk was just as clueless about what's happening with as my manager and myself. Once I get the dummy spreadsheet completed I'll upload it so you can get a better idea of what's going on.

Also...the Helpdesk here seems to think it may be an Excel 97 issue.

8. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
what happens if you just use manual calc

Joined:
Feb 12, 2007
Messages:
35
ETAF,

It took me a while but here's an example of what I'm working with. If you sum each table separately, they should all equal the same number of deficiencies.

File size:
45.5 KB
Views:
130
10. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
strange I could not get the table starting B77 to work with the > took > out and it seemed to work OK

I wonder if its related to the use of > at all

try with out > sub for GTR and see if that then works OK

11. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
yes it is the >

I just replaced the code to put a ' in front of the >

so
and it calculated correctly

On my simple example of you change the date to get >60 in other cells it does not calculate - so even in my simple 2003 version it does not work

the ' may be a good subsitute

Joined:
Feb 12, 2007
Messages:
35
OMG....just that simple? I tried it and it seems to work just fine for the Aging portion. Thanks a lot...that's been a real head scratcher.

Did you notice also that the total for "Total Deficiencies" (at H34) and the total for "Deficiencies by Business Process" (at F73) are not equal.

13. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
no i didnt look
i'll have a look now

14. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
you should have 2 entries for 02 order capture

you have "in progress" instead of "not started" in E42

Joined:
Feb 12, 2007
Messages:
35
ETAF....thank you very much! Sometimes all it takes is another set of eyes. Thanks again, this resolves my issues for now. Next I'll have to figure out why when I add my macro to generate charts, that my coworkers get a runtime error but it works for perfect for me.

As Seen On