stdev.p does not get correctly processed

Issue #448 wontfix
Will Holland created an issue

I've made a spreadsheet, and needed to get the standard deviation "STDEV.P" of some values.

I've set the cell value to: "=STDEV.P(...)", however, when I open excel, it gives me a 'name' error.

On further investigation, "=STDEV.P(...)" gets mapped within openpyxl or excel (I don't know which) to "=_xludf.STDEV.P(...)", which is incorrect. It should get mapped to: "=_xlfn.STDEV.P(...)"

When I explicitly set the value to "=_xlfn.STDEV.P(...)" the function works.

I believe that openpyxl should do this mapping, rather than the user...

Comments (6)

  1. CharlieC

    This is somewhat incomplete but it looks like your depending upon an extension to the formulas that was added after the initial specification.

  2. CharlieC

    The important thing is that it is an extension of the original specification. In Excel it looks like one thing but is actually another.

    Do you have a suggestion for how the documentation can be improved?

  3. CharlieC

    It's a little verbose. I think it's more important to explain how we handle formulae: naively. And then what to in case of errors. Probably an even more common error will be down to Excel's localisation of formulae so the German version of the example from the updated specification is =STABW.N(123;124;143;173;112;109). You can imagine the errors that can lead to.

    It may also be worth looking at the options you have for saving the file. Apparently, Office 2013 can read and write the socalled Strict version of the specification which should include this formula. openpyxl will currently fail to read such a file but it might be interesting to see whether the prefix is dropped. Unfortunately, I can't test this myself as I only have Office for Mac

  4. Log in to comment