Sign in
 
Home20132010Other VersionsLibraryForumsGallery
TechCenter >
> 
> 
STDEVP (STDEV.P) returns different results in Excel 2007 and Excel 2010

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

  • Tuesday, March 13, 2012 10:02 PM
     
     
    Sign In to Vote
    0

    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.

     

All Replies

  • Thursday, March 15, 2012 8:36 AM
    Moderator
     
     
    Sign In to Vote
    0

    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 1:39 PM
     
     
    Sign In to Vote
    0

    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

     
  • Friday, March 16, 2012 12:55 AM
    Moderator
     
     
    Sign In to Vote
    0

    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



     
  • Tuesday, March 20, 2012 3:50 PM
     
     
    Sign In to Vote
    0

    I've attached a screen shot showing the formulas.  The original .xls file is long gone.

     
  • Wednesday, March 21, 2012 2:09 AM
    Moderator
     
     
    Sign In to Vote
    0

    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:02 PM
     
     
    Sign In to Vote
    0

    those formulsa give the same results in both Excel 2007 and 2010.

     
  • Thursday, March 22, 2012 2:47 AM
    Moderator
     
     
    Sign In to Vote
    0

    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



     
  • Friday, March 23, 2012 4:44 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi,

    Have you tried that?

    Best regard,

    Rex Zhang

    TechNet Community Support

     
  • Wednesday, April 04, 2012 6:31 PM
     
     Answered
    Sign In to Vote
    0

    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
    •