Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    London, United Kingdom
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Not calculating (2000 SR1)

    First of all, in a few short hours I am off on my Christmas holidays, but I don't want to still battle this problem when I get back <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

    So I need some help please

    We have been coming across Excel worksheets recently that are not calculating correctly.
    This example is to keep it easy to understand:
    You have a column of 15 figures all with the value of 10
    You use the count function. You should get 15
    Sum you should get 150.
    What we have been getting are count totals of 12 and sum of 120.
    For some reason it is not recognising the values in cells. The only thing I have been able to find is that sometimes people have centred the values and the something has caused the formatting of 2 of the cells to show as text. Even if you change the formatting back to number or general, the only way to solve the problem is to use Edit/Clear All and re enter the information.

    Any ideas on what is happening.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not calculating (2000 SR1)

    This is a well-known problem; it occurs most often with data that has been imported from another file format.

    The easiest solution is:

    - Select one column of "problem" data.
    - Select Data/Text to Columns...
    - Select the Fixed Width option and click Finish.

    or, alternatively:

    - Enter the number 1 into an empty cell.
    - Copy this cell to the clipboard.
    - Select the area containing "problem" cells.
    - Select Edit/Paste Special...
    - Select the Multiply option and click OK.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    London, United Kingdom
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not calculating (2000 SR1)

    Hans
    That seems to sort out some of the problems. In one workbook we've got the formula on our summary sheet =If(T24=Master!T647,True,False) T24 does equal cell T647 on the Master Worksheet but we keep getting "False", yet a similar formula but based on different cell references) in the row above gives the correct response. Is there something wrong in our formula? We may have been looking at it too long. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not calculating (2000 SR1)

    Hetty,

    Do T24 on the summary sheet and T647 on the Master sheet contain constants (fixed values), or are they the result of formulas? If the latter is the case, bear in mind that Excel works with a finite precision; two formulas that should theoretically yield the same result may in practice return slightly different values. One might return 2.24 and the other 2.24000000001. If the cells are formatted to display 2 decimals, or if the column is narrow, you wouldn't see that difference.

  5. #5
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    London, United Kingdom
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not calculating (2000 SR1)

    Ah Ha... The results are based on rather large formula and which are shown with fixed decimal.

    Santa's first name is Hans

    Thank you <img src=/S/kiss.gif border=0 alt=kiss width=34 height=15>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not calculating (2000 SR1)

    Ok, then you might check for "almost-equal":

    =If(Abs(T24-Master!T647)<0.000001,True,False)

    Replace 0.000001 by a very small value you find acceptable. You can also put this value into a cell and compare to that, or define a name SmallValue that refers to =0.000001 or whatever value you choose.

  7. #7
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    London, United Kingdom
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not calculating (2000 SR1)

    It worked!

    Christmas Pudding - here I come!

    Thank you

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not calculating (2000 SR1)

    Merry Christmas!

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not calculating (2000 SR1)

    Or -of course-:

    =Abs(T24-Master!T647)<0.000001
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •