none
A simple sum formula is not calculated correctly RRS feed

  • Question

  • Hello everyone!

    I am using a simple formula in a custom field to sum numbers "[Number2]+[Number3]+[Number4]+[Number5]". Unfortunately it is not calculating correctly. I have changed field type from NumberX to CostX, but it does not help. So far I have not found any solution.

    Below I have attached picture where it is possible to see what formula is used, and using calculator it is possible to see that first totals row in Number6 field is not calculated correctly, the right value should be 309390. In second picture it is possible to see for how many tasks MSP gives wrong values. 

    Is there anything I can do to get correct values?

     

    Kind regards,

    Raivis

    Thursday, September 22, 2011 6:56 PM

Answers

  • If you want it all to tick and tie, you could use this formula:

    Round([Number1],2)+Round([Number2],2)+Round([Number3],2)+Round([Number4],2)+Round([Number5],2)

     


    Reid McTaggart Partner DeltaBahn LLC
    Tuesday, September 27, 2011 8:19 PM

All replies

  • Hi Raivis,

    Welcome to this Microsoft Project forum:)

    It looks like simple rounding errors.  Without knowing where the data in  the numbers columns comes from, I can't be specific.  I guess they are costs  calculated by Project which use raw data and round to 2 decimal places. Number 6 sums would thus come by adding the raw data and rounding the  sum.

    FAQs, companion products and other useful Project information can be  seen at this web address:  http://project.mvps.org/faqs.htm

    Hope this helps - please let us know how you get on :)

    Mike Glen
    MS Project MVP
    See http://tinyurl.com/2xbhc for my free Project Tutorials

    Thursday, September 22, 2011 8:11 PM
    Moderator
  • Hello Mike! Thank you for your kind support!

    Cost1, Cost2, Cost3 - a simple costs which are entered by the user;

    Cost4 = [Cost1]+[Cost2]+[Cost3];

    Number1 = [Cost2]*0.07;

    Number2 = [Cost1]+[Cost2]+[Cost3]+[Number1];

    Number3 = [Number2]*0.1;

    Number4 = [Number2]*0.05;

    Number5 = [Cost1]*0.2409;

    Number6 = [Number2]+[Number3]+[Number4]+[Number5];

     

    I have created an image below, which shows basically the same above information. Is there any way it can be calculated correctly?

     

    Raivis



    • Edited by R-aivis Friday, September 23, 2011 4:08 AM
    Friday, September 23, 2011 4:07 AM
  • I think it's as I thought - simple rounding errors. Do pennies in the
    thousands that you're creating really matter?

    Mike Glen
    Project MVP
    See http://tinyurl.com/2xbhc for my free Project Tutorials
    Friday, September 23, 2011 2:13 PM
    Moderator
  • Hi!

    Well.. some clients are kind a scrupulous, but I do understand your point.

     

    Raivis

    Tuesday, September 27, 2011 4:43 PM
  • If you want it all to tick and tie, you could use this formula:

    Round([Number1],2)+Round([Number2],2)+Round([Number3],2)+Round([Number4],2)+Round([Number5],2)

     


    Reid McTaggart Partner DeltaBahn LLC
    Tuesday, September 27, 2011 8:19 PM
  • Thank you for your reply, but it did not help. This is getting more serious, I have simplified used formulas and still MSP makes mistakes, please see below.

    Definition of fields:

    Cost1 - manually editable field. Calculation for task and group summary rows - Rollup: Sum;

    Cost2 - manually editable field. Calculation for task and group summary rows - Rollup: Sum;

    Cost3 - manually editable field. Calculation for task and group summary rows - Rollup: Sum;

    Cost 4 - [Cost1]+[Cost2]+[Cost3]. 

     

    If calculation for task and group summary rows - Rollup: Sum, then the result is Cost4 = 240 207.26. See the picture below.

    But if calculation for task and group summary rows - Use formula, then the result is Cost4 = 240 207.20. See the picture below.

    The second version with the total sum Cost4 = 240 207.20 is the correct result.

    Can anyone tell me how can this happen. Remember that all Cost1-Cost3 fields are manually entered with 2 decimals. I'm really surprised.

     

    Kind regards.

    Saturday, October 8, 2011 11:15 AM
  • Hello,

    Starting back a few steps.  In quickly scanning your posts I don't see mention of Project version of service pack information.  What version and SP or CU do you have applied?

    Julie

    Saturday, October 8, 2011 11:32 AM
    Moderator
  • Microsoft Project 2010 (14.0.6023.1000) SP1 MSO (14.0.6106.5005), 32 bit.

    Is this information sufficient?

     

    Raivis

    • Edited by R-aivis Sunday, October 9, 2011 3:38 PM
    Sunday, October 9, 2011 3:29 PM
  • Hi,

     

    Sorry for the delay.  You do clearly have SP-1 installed.  So, a couple of more questions:

    Does this occur with all files -- can you create a new simple file and cause the same issue?

    Tuesday, October 11, 2011 3:22 PM
    Moderator
  • With the same versions, except 64 bit instead of 32 bit, I cannot repro this problem.  Julie's questions are good ones.
    Reid McTaggart Partner DeltaBahn LLC
    Tuesday, October 11, 2011 3:51 PM
  • Hi!

    No result.

    Here is what I did. I copied Cost1, Cost2 and Cost3 columns to excel. I used this kind of formulas "TRUNC(A2;2)" and made new three columns, so I would be sure, that there are only 2 decimals. Then I created completely new Project file, I pasted Name column from previous MSP file using paste special - text, so I'd be sure that no other information rather that values would be copied. I added all three cost columns to the view and modified rollups to sum. Then I copied data from excel to MSP using paste special. Then I indented tasks so the correct total sum would appear. Then I added Cost4 and wrote formula [Cost1]+[Cost2]+[Cost3]. In the end it still calculates Ls 240 207.20 when I have "Use formula" selected and  Ls 240 207.26 when I have "Rollup sum" selected.

    Here (http://www.projekts.lv/msp/2011.10.12_WrongSumSimple.mpp) you can see the new file itself.

     

    Any idea what else can I try?


    • Edited by R-aivis Monday, October 17, 2011 8:03 PM
    Wednesday, October 12, 2011 7:30 AM
  • I tested your file on my computer, and it calculates correctly (Ls 240,207.20) both ways.

    Perhaps someone with a 32-bit computer will test it as well.

    I suspect something is wrong with your computer or with your installation of Project.  Try it on other computers in your organization.  Or try running the Office Repair feature.  Run diagnostics on your computer.


    Reid McTaggart Partner DeltaBahn LLC
    Wednesday, October 12, 2011 12:34 PM
  • Thank you Reid for your help. That is good to know that it calculates correctly both ways.
    Wednesday, October 12, 2011 12:36 PM
  • Sorry to disappoint but when I open the file, I see the exact error on the summary task line.  When the rollup is set to "Sum" the Project summary line is incorrectly calculated.  Add a new cost field, use the same formula used to calculate Cost4 and set the summary task to "Use formula" and I get the correct answer.

    Windows 7 Professional 64-bit

    Project 2010 - 32 bit SP-1.

    I don't have time right now (sorry) to try to repro on a new file using your values.  When I can -- I'll post back.

    Julie

    Wednesday, October 12, 2011 1:53 PM
    Moderator
  • Julie's reply (combined with my test) leads me to suspect that this is a problem with the 32-bit version of Project.

    I'm on Win 7 64-bit, and Project Pro 2010 SP1 64-bit.


    Reid McTaggart Partner DeltaBahn LLC
    Wednesday, October 12, 2011 1:55 PM
  • Sorry for the delay -- yes.  I can repro this in a simple file (4 tasks).  The issue seems that the values in the Cost2 and Cost3 fields in my case are not calculating correctly.

    I have sent a message to the project team.  I'll let you know what I hear.

    Wednesday, October 12, 2011 4:53 PM
    Moderator
  • Hi!

    No result.

    Here is what I did. I copied Cost1, Cost2 and Cost3 columns to excel. I used this kind of formulas "TRUNC(A2;2)" and made new three columns, so I would be sure, that there are only 2 decimals. Then I created completely new Project file, I pasted Name column from previous MSP file using paste special - text, so I'd be sure that no other information rather that values would be copied. I added all three cost columns to the view and modified rollups to sum. Then I copied data from excel to MSP using paste special. Then I indented tasks so the correct total sum would appear. Then I added Cost4 and wrote formula [Cost1]+[Cost2]+[Cost3]. In the end it still calculates Ls 240 207.20 when I have "Use formula" selected and  Ls 240 207.26 when I have "Rollup sum" selected.

    Here (http://www.projekts.lv/2011.10.12_WrongSumSimple.mpp) you can see the new file itself.

     

    Any idea what else can I try?

    Hello Raivis,

    I opened the mpp file (http://www.projekts.lv/2011.10.12_WrongSumSimple.mpp) with Project 2010 32-bit (XP). I see the value 240207.26 in Cost4 field initially but it becomes 240207.20 as soon as I press on F9 (or as soon as I toggle between "Use formula" and "Rollup: Sum") and then I can never get .26 again.

    I believe you do not have August hotfix on your system. IMO, pls apply it and retry.

    Hope it works.

     

     


    Regards. --Ismet
    Wednesday, October 12, 2011 6:49 PM
  • Hi Ismet,

    I've installed the August hotfix to my installation of Project 2010 and no joy.  Creating a new file with a summary task and one subtask and using the CostX field shows the wrong data on "sum".

    I am using Windows 7 whereas it appears you are using XP.  That may have something to do with it.

    Wednesday, October 12, 2011 7:23 PM
    Moderator
  • Thank you Julie.
    Regards. --Ismet
    Wednesday, October 12, 2011 7:28 PM
  • Hello colleagues!

    Did you run windows update to get august hotfix?

    I'm running win7 32bit.

    Julie, any answer from the project team?

     

    Sincerely,

    Raivis

    Wednesday, October 12, 2011 7:32 PM
  • Did you run windows update to get august hotfix?
    Sorry, I guess I misled you. They've already got August hotfix. 

    Regards. --Ismet
    Wednesday, October 12, 2011 7:52 PM
  • Hi Raivis,

    No hot fixes do not install through Windows updates and I cannot find any information in that hotfix package that addresses this issue.  I did download as a trial and it did not seem to have any effect on either an existing file nor on older files exhibiting this issue.

    For the current hot fix/CU data see:

    http://technet.microsoft.com/en-us/office/ee748587.aspx

     

    I am waiting to hear more solid information from the project team and will update you when I hear.


    Wednesday, October 12, 2011 7:56 PM
    Moderator
  • Thank you Julie! Let's hope they will provide you with good information.

    Wednesday, October 12, 2011 7:58 PM
  • Update from the team -- it does not appear on a 64 bit installation of Project, as Reid suggests -- although I find it curious that Ismet can "fix" the problem with a recalculate (F9) command on XP.

    I'll keep everyone here posted about further developments.

    Wednesday, October 12, 2011 7:59 PM
    Moderator
  • although I find it curious that Ismet can "fix" the problem with a recalculate (F9) command on XP.

    That's correct. Even a simple expand/collapse action on Project Summary task resets the value to .20.

    Regards. --Ismet
    Wednesday, October 12, 2011 8:09 PM
  • Quick update.  The issue has been filed as a bug in the 32 bit version.
    Thursday, October 13, 2011 2:48 PM
    Moderator
  • I see.. Well, MS has a work to do :)!
    Thursday, October 13, 2011 2:50 PM
  • Quick update.  The issue has been filed as a bug in the 32 bit version.
    I hope they fix it with the next hotfix release. I've just tested it on W7 (32-bit) SP1 with Project 2010 (fully updated) a couple of minutes ago in another computer but hitting on F9 surprisingly changes the value in the cell to .20 and then the problem goes away.  It seems like a pretty complex issue.

    Regards. --Ismet

    Thursday, October 13, 2011 5:27 PM
  • I see.. Well, MS has a work to do :)!

    Hello R-aivis,

    I've just opened the file with Project 2013, then exported the custom cost field data to Excel. I see values with more than two decimal digits in the fractional part in Excel. When the top value is xxx.26, I hit F9; Project recalculates and the top value becomes xxx.20. Now exporting values to Excel reveals that Project has perfectly rounded all the values down to two decimal places, which is the default precision for the cost fields. The result does not change with either one of the two methods; that is Roll-up:Sum or Use formula. 

    I do not know the history of the file, but IMHO, Project does not produce such doubles by user entry. It is a sign of file corruption. And Project 2013 fixes it perfectly. 

    Kind Regards, Ismet









    Thursday, March 20, 2014 3:23 PM