Tech Support Guy banner
Status
Not open for further replies.

Solved: EXCEL 97 Not Calculating

4K views 15 replies 2 participants last post by  etaf 
#1 ·
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.

Please help....this is bugging the crap out of me because I've seen the formulas work.
 
#3 ·
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).
 
#7 ·
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.
 
#10 ·
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 ·
yes it is the >

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

so
=IF(W10<-60,"'>60 Days",I..........
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
 
#12 ·
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.
 
#15 ·
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.
 
#16 ·
your welcome

:up:
Would you now mark this thread solved - which you have the power to do from your first post.
goto
Thread tools>
Tick - Mark Solved

Thanks :up:

when you get around to it - start a new question re the macros and graphs
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top