none
Excel 2010 Bugs

    Question

  • Hello, 

    I have recently found that excel 2010 at times does not calculate the formula correctly. I am using a "COUNTIF" function have three cells as range. The result returned by the formula is "3" while actually the criteria appears only two times out of the three cells. There is this another error where I add one cell from each of about 10 different sheets in the work book to obtain a total. I have used the evaluate formula function and it appears that it calculates the values correclty untill the last addition, when it adds the last cell value it returns the total/final result as "0". I have edited to formula to remove few cells and add few cells but the result ends up to "0". I am worried as some of my analysis with data is already sent out to clients. 

     

    Could you please provide suggestions.

    Thank you

    Vinod


    Tuesday, July 26, 2011 8:36 PM

Answers

  • The reason you get a 3 instead of a 2 for "REAR END" is, that cell G13 still contains the value in the background. You'll see it, when you select F11:G13 and remove the merge. You should COUNTIF only for the F column.
    • Marked as answer by winodv Monday, August 1, 2011 5:11 PM
    Monday, August 1, 2011 4:53 PM

All replies

  • Closing and Re-opening the spreadsheet fixed the sum issue. When it comes to the CountIF, this the current formula "Countif(F11:G13,j17)" , the columns F and G are MERGED. IF i change the formula to "COUNTIF(F11:F13,J17)", I am getting the correct results. While I am using the previous format of the formula at other place and getting the correct results, I wonder why this is happening just for this cell.
     
    V
    Wednesday, July 27, 2011 2:23 PM
  • Hi Vinod - Thanks for posting your formula, would it be possible to post the contents of the cells F11:G13 and J17?  I think this has something to do with merging the cells, but want to make sure we understand all of the parameters the formula is looking at.

    Thanks!

     


    Please remember to mark this as "answer" if it resolves/answers your issue.
    Saturday, July 30, 2011 12:37 AM
    Answerer
  • Ryan the range in the Countif formula F11:G13, where the columns F and G are merged. The contents in F11 is 'Angle", and F12 & F13 is "SideSwipe". The parameter/criteria in the countif Formual is J17 which had a Value of "SideSwipe". The result should have been 2 but it returns 3. Please keep in mind that this formula works fine with other sheets which have similar formatting. For some reason this is the only sheet that is showing a incorrect value.

    Thank you for looking in to this. 

    Vinod


    V
    Monday, August 1, 2011 12:35 PM
  • Hi

    I must be honest I cannot reproduce the issue you have reported using the information you have provided.

    Can you find an empty part of your sheet and enter the formula

    =F11

    then copy this down two rows and across to the next column.  This way we can see exactly what is in the range F11:G13

    I'd expect you to get

    Angle          0

    SideSwipe   0

    SideSwipe   0

    However the comments posted suggest otherwise.

     


    G North MCT
    Monday, August 1, 2011 1:19 PM
  • Hello North, 

    I have encountered this error in one of the sheets. I have deleted other sheets and a copy of the sheet in discussinon can be downloaded from our FTP using  the following link. 

    The cell in discussion is J19. Please note that this link will be valid only for 2 days. 

    https://gmb.egnyte.com/h-s/20110801/af97bf3ac33549c2

    Thank you

    Vinod

     


    V
    Monday, August 1, 2011 1:40 PM
  • Hi

    Have tried the link but get a link no longer valid message.


    G North MCT
    Monday, August 1, 2011 2:18 PM
  • Try this link

    https://rapidshare.com/files/835055475/detailed_crash_summaries_5-1_to_5-15.xls


    V
    Monday, August 1, 2011 4:25 PM
  • The reason you get a 3 instead of a 2 for "REAR END" is, that cell G13 still contains the value in the background. You'll see it, when you select F11:G13 and remove the merge. You should COUNTIF only for the F column.
    • Marked as answer by winodv Monday, August 1, 2011 5:11 PM
    Monday, August 1, 2011 4:53 PM
  • Alexander, 

    I see what you are saying, but I thought Excel always replaces the value when you merge cell.

    Wasnt this supposed to be the case


    V
    Monday, August 1, 2011 4:56 PM
  • Yes, a very strange behavior that I can't explain. When I unmerge the cells F13 and G13, merge and unmerge them again, the value in G13 gets deleted. Right after the merge, as you can see by referencing =G13.
    Monday, August 1, 2011 5:06 PM
  • It looks like we've got the answer/workaround....wondering, winodv, how are the values being inserted into these cells and then merged?  Like Alexander said, when doing it thru the UI it should pop up a message on merging and tell you G13 is being deleted.  Is any of this being done by code?
    Please remember to mark this as "answer" if it resolves/answers your issue.
    Monday, August 1, 2011 8:10 PM
    Answerer
  • All the data is being typed manually Ryan, I encountered few more scenarios today. I unmerged the cells and found that the two cells that were previously merged had the same data after un merging them. 

    Again all the data is manually typed in. 

     

    The cells are mergerd before entering the data. If that answers your question

    Thank you


    V
    Monday, August 1, 2011 8:13 PM
  • On Mon, 1 Aug 2011 20:13:58 +0000, winodv wrote:
     
    >
    >
    >All the data is being typed manually Ryan, I encountered few more scenarios today. I unmerged the cells and found that the two cells that were previously merged had the same data after un merging them. 
    >
    >Again all the data is manually typed in. 
    >
    >
    >The cells are mergerd before entering the data. If that answers your question
    >
    >Thank you
    >V
     
    I think HansV, an MVP in another group, has produced a method to reproduce the problem -- and I can confirm that the problem occurs in Excel 2007 also.
     
    If the Merge Cells is applied using the format painter, or by copying a merged cell and then doing a Paste Special/Format over the unmerged cells, rather than using the Merge Cells command, the data in the right hand cell is preserved.
     
     

    Ron
    Monday, August 1, 2011 11:31 PM
  • I wonder if it is suppose to be function the way it is. When merging two cells with values , excel prompts a message saying that , it would keep the upper left hand cell value. When you are copying the format shouldn't it be the same case?

     

     


    V
    Tuesday, August 2, 2011 12:52 PM
  • I would think copying a format should be the same as applying it.  But it's not in the case of Merge cells, at least for 2007 and 2010 on our respective machines.

    One could argue that you don't need that message since you will not be losing any data!  So perhaps the real question is why does copy format work differently with merge cells, than does applying the merge directly?

     

     


    Ron
    Tuesday, August 2, 2011 7:12 PM
  • I've also found a bug :

    When I use sum function and search the rang with rang or equation.rang, the result is not valid.

    See the linked file function_sum_rang_bug

    To avoid this problem, i have used the function round()



    Monday, March 26, 2012 11:41 AM