1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: EXCEL 97 Not Calculating

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

Thread Status:
Not open for further replies.
Advertisement
  1. Warne57032

    Warne57032 Thread Starter

    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.

    Please help....this is bugging the crap out of me because I've seen the formulas work.
     
  2. etaf

    etaf Moderator

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

    Warne57032 Thread Starter

    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. etaf

    etaf Moderator

    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:

  5. Warne57032

    Warne57032 Thread Starter

    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. etaf

    etaf Moderator

    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
     
  7. Warne57032

    Warne57032 Thread Starter

    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. etaf

    etaf Moderator

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

    Warne57032 Thread Starter

    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.
     

    Attached Files:

  10. etaf

    etaf Moderator

    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. etaf

    etaf Moderator

    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
     
  12. Warne57032

    Warne57032 Thread Starter

    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. etaf

    etaf Moderator

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

    etaf Moderator

    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
     
  15. Warne57032

    Warne57032 Thread Starter

    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.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/543453

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice