# Formula results not adding correctly?

1. ## 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  Register To Reply

2. ## 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  Register To Reply

3. ## 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?  Register To Reply

4. ## 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  Register To Reply

5. ## 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?  Register To Reply

6. ## 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.  Register To Reply