Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

was reviewing an "excel challenge" and found an question that I can obtain an answer with excel 2003+ but not in LibreOffice Calc 3.5.4.2. I'm interested in why the solution doesn't work in Calc but does in Excel. For question 6:

{=STDEV(Range1/MID(Range2,10,4))}

And yes, I understand that it is a CSE formula and entered in the formula with ctrl+shift+enter in both excel and calc. Excel returns a result, Calc returns #DIV/0!.

Saving / reloading the file in xls or ODS has no effect in Calc - and Calc is configured for cells to auto calculate (Tools > Cell Contents > AutoCalculate on) so that isn't the culprit.

Any ideas why?

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

Difference is that with LibreOffice, MID(Range2,10,4) returns "text" elements, whereas in Excel the same formula returned as numeric elements.

{=STDEV(Range1/VALUE(MID(Range2,10,4)))}

forces LibreOffic (and Excel) to interpret elements as numeric - and returns the solution.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.