+ Reply to Thread
Results 1 to 6 of 6

Formula results not adding correctly?

  1. #1
    Forum Contributor
    Join Date
    04-09-2015
    Location
    Jamestown, TN
    MS-Off Ver
    2010
    Posts
    111

    Formula results not adding correctly?

    HI all,
    I have attached a spread sheet I have been working on. Basically my summary formulas on (Finish Date) are not adding up correctly and I don't understand why. Such as F12 should be a date of 9/21 and F80 Should be the same, however, they have a date of 11/6 which is basically the latest date in full column. I recently added the second set of summary and not sure if I am doing this correctly. The dark Grey Row is for summary of a section, and the light gray rows are summaries of the sub sections. I think if you take a look at it you will see what I mean. The Dark gray rows should calculate all the light gray summary rows between the dark gray rows and the light gray rows calculate the white rows between the light gray rows.
    Would really appreciate some assistance in figuring this out. Have no idea why it doesn't calculate correctly.
    Thanks
    Dave
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    04-09-2015
    Location
    Jamestown, TN
    MS-Off Ver
    2010
    Posts
    111

    Re: Formula results not adding correctly?

    There has got to be an error with my formulas in col D,E and or G for this not to add up correctly but I have tried everything I can think of and at a loss here. Any one have any ideas?
    Thanks

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,881

    Re: Formula results not adding correctly?

    It looks like it is calculating correctly, based on the formulas you give it.

    The first tool I used in looking at this is the evaluate formula tool. If you are unfamiliar with this tool: https://support.office.com/en-us/art...la_one_step_at

    F12 appears to simply add the number of days in E12 to the date in D12. If you add 70 days to the end of August, you should get a date in early November.

    D12 seems to be calculating the earliest data between this entry and the next instance of "task category 1" in C (row 90) and the result is correctly 29 August.

    E12 seems to be calculating the number of days between the latest date in column F of the same range (6 November in F80) and the date returned in D12. There are about 70 days between 6 November and 29 August, so this seems correct.

    I didn't go farther than that, but so far, the calculations seem correct.

    If you continue using the formula evaluate tool as you drill down through the calculations, can you find the cell that seems to be causing the error?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Forum Contributor
    Join Date
    04-09-2015
    Location
    Jamestown, TN
    MS-Off Ver
    2010
    Posts
    111

    Re: Formula results not adding correctly?

    I will try that tool, didn't know it existed. In any event, I am sure it add up correctly for which it was told, yet that is not the result I had wanted and not sure how to get to the result I want?
    Thanks

  5. #5
    Forum Contributor
    Join Date
    04-09-2015
    Location
    Jamestown, TN
    MS-Off Ver
    2010
    Posts
    111

    Re: Formula results not adding correctly?

    I am looking at this and I just don't understand how this is happening on this. If you look at F224, it is a summary date of 11/6 and it should be 10/1, similar to the cells above it such as F214 which is the result I am looking for. Its the same formula used for both summaries, yet one does not give me the correct number of days. I don't have any idea how to fix this. Any further assistance would be greatly appriciated. It appears at the end of the summary lines before the next level of summary, the date always is 11/6 and that is not correct as like the example above. Lost Here?

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,881

    Re: Formula results not adding correctly?

    Another useful debugging tool that does not get much air time: R1C1 notation. One of the "difficulties" of working in A1 notation is that, when relative references are used, copies of the formula "look" different. In R1C1 notation, copies of formulas always read exactly the same. F214 and F224, indeed have the exact same formula. However, these are relatively simple addition functions based on D214 and E214 and D224 and E224. Looking at these cells, the formulas in D214 and D224 are different, and E214 and E224 are different. The lookup/MATCH() parts of these formulas in row 214 start their lookups 2 rows below row 214 (row 216). However, in the row 224 formulas, the lookups begin in the same row (row 224). If you run through the formula evaluator, this causes these lookups to return row "1", which is the same row as the lookup function, which causes the INDEX() part of the function to return the entire remaining column, rather than the small subset.

    I also notice that, because the search string is "sub task", even changing the formula to the same as the 214 formula may still be in error. Copying the formula from 214 to 224 causes the lookup to find the "next" instance of "sub task" which is below the "task category" entry in row 252. So the MAX and MIN functions start looking beyond the current "task category" into the next "task category". I am guess that the lookup should stop searching at the "task category" row. If this is correct, in addition to correcting the formula, you might add a "dummy" sub task row above each "task category", so that the formulas in D and E will not search beyond the next task category.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula not adding correctly?
    By snappyfish in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-26-2015, 04:58 PM
  2. IF(OR( Formula not adding correctly
    By mwilson55 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-19-2013, 10:19 AM
  3. [SOLVED] Adding cells containing the results of formula (without any text)?
    By starupabove in forum Excel General
    Replies: 9
    Last Post: 08-21-2012, 09:29 PM
  4. Adding results of formula cells
    By XH558 in forum Excel General
    Replies: 5
    Last Post: 11-29-2011, 11:58 AM
  5. Formula Not Adding Correctly,
    By donnydorko in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-09-2009, 07:35 PM
  6. SUMPRODUCT formula not adding correctly
    By ExcelNewby in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 02-10-2008, 08:41 PM
  7. SUM problem: adding formula results
    By JPN5804 in forum Excel General
    Replies: 2
    Last Post: 11-28-2005, 12:29 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1