• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel formula not calculating correctly [SOLVED]

Kimber

Kimber

Member
I have a few cells where the sum calculation is not working correctly. When I highlight the cells, the total (sum) in the status bar is correct, however, the cell with the formula in it does not give an accurate answer (sum.) Please help! I have verified that cells involved are formatted as numbers
 
Test... copy the values in a new workbook. Apply the same sum. If it returns the same mistake, then something might be wrong with the formula at hand else...
I have had this once in 20 years of Excelling... Turned out the sheet was somehow corrupted. At least that was the only "reasonable" explanation I could find. Rebuilding the sheet was the only solution.
 
Retype the numbers
Or
Do a put a 1 in a cell
copy
paste special values over the range
multiply

it is likely one of the numbers is text
 
Thank you all for your replies. I am attaching a copy of the sheet. I have highlighted the cells that are giving incorrect totals. You will notice there are three subgroup totals and only the total row in those groups is included in the top total. To be honest, I have not tried a formula that includes all individual cells and omitting the subgroup total rows. Hui, I have checked for text formatting and I have not found that to be the case. GraH - Guido, I have had this situation before and I did have to recreate the sheet. I wish I knew the cause. AliGW, not a rounding problem because the answers are too far off.
 

Attachments

  • Total Rows not working correctly.xlsx
    61.8 KB · Views: 7
Kimber
Where do You compare Cell C4 -value?
.... That You can write, it gives incorrect totals.
C4 ... is sum of 17 different cells.
Do C8, C96 or C100 belong to somewhere?
 
Explain why you think J4 should be 1839. Makes no sense to me whatsoever! Even if I add every fourth cell in J from row 24 to row 124, I still don't get that much - all I get is 1710.13. You need to explain your arithmetic! As far as I can see, there is nothing wrong with the answer that Excel is giving based on what you are asking it to do.
 
C4 is correct at 867.3
why is it wrong?
Have you included cells you shouldn't have
Have you left out cells that you should've included?
 
vletm said:
Kimber
Where do You compare Cell C4 -value?
.... That You can write, it gives incorrect totals.
C4 ... is sum of 17 different cells.
Do C8, C96 or C100 belong to somewhere?
Click to expand...

Thank you for your reply. When I highlight each of the cells referenced in C4's formula, I get a different answer (1455.2.) Actually, row 3 is not totaling correctly, either, as C3 should be 1637.0 rather than 1095.0. I learned about the problem when comparing the total from an accounting document. This is a new worksheet I have just created for the last nine months and these are actually the only two rows that are not totaling correctly. I have two other summary sections like C3-S6 that match the numbers from accounting.
 
AliGW said:
Explain why you think J4 should be 1839. Makes no sense to me whatsoever! Even if I add every fourth cell in J from row 24 to row 124, I still don't get that much - all I get is 1710.13. You need to explain your arithmetic! As far as I can see, there is nothing wrong with the answer that Excel is giving based on what you are asking it to do.
Click to expand...

Thank you, AliGW. If you hold the CTRL key and click the second row of each "group" (shaded or non-shaded) and DO NOT INCLUDE the TOTAL SUBGROUPS rows, you will get 1839. I failed to highlight the BUDGET row (row 3) as all of those totals are incorrect as well. As I stated to vletm, the correct totals are from accounting reports and I discovered the problem with the total after checking all of my data entry. I have two other sections (not included in my upload) that work correctly, so I am baffled.
 
Hui said:
C4 is correct at 867.3
why is it wrong?
Have you included cells you shouldn't have
Have you left out cells that you should've included?
Click to expand...

Thank you for the response, Hui. I know the totals are incorrect because I checked them after comparing to my accounting department's report totals. The cells I have excluded are included in the subgroup totals. If you hold the CTRL key and click the second row of each "group" (shaded or non-shaded) and DO NOT INCLUDE the TOTAL SUBGROUPS rows, you will get 1455.2 for cell C4. I have closed and reopened this file to see if it was not updating but I have my settings on auto calculate and all cells are formatted as numbers.
 
Kimber
I tested AGAIN to do 'highlight' as You - and - I still got same 867.3!
Did You reply ... what is Your 'correct' C4-value?
Check this file... those 'pink'-cells belongs to C4.
 

Attachments

  • Total Rows not working correctly.xlsx
    61.3 KB · Views: 3
vletm said:
Kimber
I tested AGAIN to do 'highlight' as You - and - I still got same 867.3!
Did You reply ... what is Your 'correct' C4-value?
Check this file... those 'pink'-cells belongs to C4.
Click to expand...

Thank you, for another prompt response. There were a few cells not included by mistake (but are included on my original sheet.) I am beginning to think my file is corrupt because the file you sent back is working (I get 1455.2 when I select all the appropriate cells to be included.)
 
I love that Excel is logical and that human error is usually the problem. I do not know how to close this question, but I want to thank those who responded. I appreciate your clear thinking and patience.
 
So you had made a mistake, then? I thought so! ;) Very glad you got it sorted, though. :)

No idea how to mark a thread as solved here - sorry.
 
Kimber
Here, normally threads do not 'close' ... but this is possible to mark as [ SOLVED ] ( done ).

Those three 'green' cells are potential ... which cannot update by others.
... and You could try change those '+ + + +' -formulas too.
Screenshot 2020-05-04 at 17.28.06.png
 
AliGW said:
So you had made a mistake, then? I thought so! ;) Very glad you got it sorted, though. :)

No idea how to mark a thread as solved here - sorry.
Click to expand...

I am not certain but I will take the blame. I do admit that I was exhausted after having worked on the worksheet for hours.
 
If your Excel formula not working correctly or not giving correct result after calculation then try this solution:

To Recalculate Entire Workbook values:
  • Go to the Formulas tab and choose the Calculation group.
  • Now from the opened Calculation group tap to Calculate Now
To Recalculate Any Specific Active spreadsheet:
  • Go to the Formulas tab and choose the Calculation group.
  • Now from the opened Calculation group tap to Calculate Sheet
 
You must log in or register to reply here.
Back
Top