none
STDEVP (STDEV.P) returns different results in Excel 2007 and Excel 2010

    Question

  • We are seeing a problem in some older spreadsheets (probably created in Excel 2003, then updated in 2007).  The STDEVP formula returns different results in Excel 2007 than it does in 2010.  The Excel 2010 result appears to be wrong.  (In 2010 STDEV.P and STDEVP both return the same wrong answer)

    The issue occurs when the STDEVP formula references a range that is itself created by formulas of the type

    If(condition a, "", a cell). 

    In 2007, the cells with "" are ignored while in 2010 they are treated as if they contained zero.  I have a sample spreadsheet which reproducse the problem, but I don't see how to attach it.

    Tuesday, March 13, 2012 10:02 PM

Answers

  • Anything that refers to the old range of source cells fails.  It appears there is something about blank cells from the old spreadsheet that makes them appear to have a value of zero rather than blank.  Since the problem only exists in a handful of older spreadsheets and we can fix them by using a macro to clear the contents of any blank cells, I am going to resign myself to never getting answer.

    I did try looking at the cell properties via VBA debug mode and nothing stood out. 

    • Marked as answer by Lynn Knight Wednesday, April 04, 2012 6:32 PM
    Wednesday, April 04, 2012 6:31 PM

All replies

  • Hi,

    We cannot reproduce the issue in Excel 2010. Could you share some details or screenshots for further test?
    In addition, have you installed the latest updates for Office 2010?
    I would suggest testing the formula with a new created file in Excel 2010.

    Sincerely
    Rex Zhang


    Rex Zhang

    TechNet Community Support

    Thursday, March 15, 2012 8:36 AM
    Moderator
  • Thanks for looking at the problem. 

    We also couldn't reproduce it in a new spreadsheet, even one created with 2007 or 2003, but the problem affects 20 or so older spreadsheets that contain a lot of historical data and that would take a long time to rebuild. 

    We do have the latest updates installed.    I 've attached a screenshot, but I'm not sure it does much good.  I have a scaled down version of the file that preserves the error, but I don't how to attach

    Thursday, March 15, 2012 1:39 PM
  • Hi,

    Your screenshot seems good. Could you give me the formulae for test?
    Have you noticed that if we do not convert the .xls  to .xlsx file, the .xls can be opened in Compatibility Mode in Excel 2010?
    How about opening the original .xls file in Excel 2010?


    Best regards,


    Rex Zhang

    TechNet Community Support



    Friday, March 16, 2012 12:55 AM
    Moderator
  • I've attached a screen shot showing the formulas.  The original .xls file is long gone.

    Tuesday, March 20, 2012 3:50 PM
  • Hi,

    Please try the following formulas in Column F, then inform the results:

    =AVERAGE(D6:D35)
    =COUNT(D6:D35)
    =STDEVP(D6:D35)
    =SQRT(DEVSQ(D6:D35)/COUNT(D6:D35))

     

    Best regards,


    Rex Zhang

    TechNet Community Support

    Wednesday, March 21, 2012 2:09 AM
    Moderator
  • those formulsa give the same results in both Excel 2007 and 2010.

    Wednesday, March 21, 2012 2:02 PM
  • Hi,

    As the screenshot show, if you recreate the formula in the same file, the return value will be correct. (i.e. 0.4737499)

    Have you set the automatic calculation?
    In Excel 2010:
    File > Options > Formulas > select Automatic under Workbook Calculation.

    How about copying the range F1:I3 to another cell in the same file to have the formulas recalculate?


    Best regards,


    Rex Zhang

    TechNet Community Support



    Thursday, March 22, 2012 2:47 AM
    Moderator
  • Hi,

    Have you tried that?

    Best regard,


    Rex Zhang

    TechNet Community Support

    Friday, March 23, 2012 4:44 AM
    Moderator
  • Anything that refers to the old range of source cells fails.  It appears there is something about blank cells from the old spreadsheet that makes them appear to have a value of zero rather than blank.  Since the problem only exists in a handful of older spreadsheets and we can fix them by using a macro to clear the contents of any blank cells, I am going to resign myself to never getting answer.

    I did try looking at the cell properties via VBA debug mode and nothing stood out. 

    • Marked as answer by Lynn Knight Wednesday, April 04, 2012 6:32 PM
    Wednesday, April 04, 2012 6:31 PM