Get different results from Excel vs LibreOffice Calc

8 replies [Last post]
mikedelcaribe
Offline
Last seen: 1 year 31 weeks ago
Title:
Joined: 14 Feb 2013
Posts: 5

I am getting different results from Excel vs LibreOffice Calc for a spreadsheet that uses the PERCENTRANK function to classify stock valuations. After calculating several different category valuations, they are multiplied together to calculate a multifactor value which is then filtered and sorted. For some reason I am getting different results on the category valuations when using Excel versus Calc. I’m not sure which one is more correct. Attached is the spreadsheet (with the blank multifactor deleted to reduce file size below 10MB).

Any ideas of what could cause this?

AttachmentSize
Trending Value Stock Selector 02-11-2013 cut blank mf rows.xls2.82 MB
eremmel
Offline
Last seen: 19 hours 5 min ago
Title: ★★★★★
Joined: 15 Dec 2011
Posts: 174
PERCENTRANK differences Calc / Excel

Have a look to the function help or the function wizard (for e.g. cell C7) in Calc and Excel.
You will find that Excel has a third (optional) argument. Consider to use that third parameter in Excel.

n/a
mikedelcaribe
Offline
Last seen: 1 year 31 weeks ago
Title:
Joined: 14 Feb 2013
Posts: 5
I tried changing the third

I tried changing the third option (significance), but didn’t see any change in the results.

eremmel
Offline
Last seen: 19 hours 5 min ago
Title: ★★★★★
Joined: 15 Dec 2011
Posts: 174
Change significance and display with more digits.

When I add/change significance to 5 and add more decimals to the figures I get same values as in Calc.
Note significance is likely influencing the calculation, but not the display-format.

n/a
mikedelcaribe
Offline
Last seen: 1 year 31 weeks ago
Title:
Joined: 14 Feb 2013
Posts: 5
Screen Shots of Excel and Calc

I changed the significance to 5 and still the results of columns c to h are different. See the attached screen shot. Which of the two screen shots has the same results that you get?

AttachmentSize
Excel Screen Capture 02-14-13.png 237.32 KB
LibreOffice Calc Screen Capture 02-14-2013.png 283.29 KB
mikedelcaribe
Offline
Last seen: 1 year 31 weeks ago
Title:
Joined: 14 Feb 2013
Posts: 5
I have uploaded and converted

I have uploaded and converted the spreadsheet to Google Doc format. The results are that it is almost exactly the same as the Libre Office results. Here is the link to the Google Docs version: https://docs.google.com/spreadsheet/ccc?key=0AuiJtMFf5iAsdFd0VVNadVJxWEd…

Any thoughts on this?

eremmel
Offline
Last seen: 19 hours 5 min ago
Title: ★★★★★
Joined: 15 Dec 2011
Posts: 174
PERCENTRANK differences Calc / Excel

I did some testing (also manual calculation of PercentageRank). See attached document. I’ll not look into google doc for it is the same doc-standard as LO.
One thing might be an issue with Excel, and that is that PercentageRank is depreciated (there are two different functions now), however LO supports only one variant. It might be that there is a setting that maps PercentageRank to one of the new functions and that might explain the difference between you and me.
Any have a look to the attached document and check the differences. I see no differences between LO and Excel when I add 5 as the third parameter in the function.
(I’ve issue uploading a document, will try later to attach)

AttachmentSize
ExplainingDifferencePercentageRank.pdf 107.88 KB
n/a
mikedelcaribe
Offline
Last seen: 1 year 31 weeks ago
Title:
Joined: 14 Feb 2013
Posts: 5
Thanks Eremmel. I am using

Thanks Eremmel. I am using Excel 2007 which only has the a single percentrank function and it is exclusive. I want to have an inclusive function to allow the ranks to cover the full range, so for now I will stick with LibreOffice Calc. Do you know if both Calc and Excel percentrank handle non-numeric cells in their range in the same way? I would want those non-numeric values in the range to not affect the results, that is to give the same results as if the were not there and the range were reduced.

Example: 1, 2, , 4, 5 The blank space would not be counted for its value and the cell would not add to the cell count.

eremmel
Offline
Last seen: 19 hours 5 min ago
Title: ★★★★★
Joined: 15 Dec 2011
Posts: 174
count() only for numbers?

It looks like that you have access to both, Calc and Excel. So check the function description and/or make a test with count().
(A question has to have some challenge before looking for an answer; at least for me)

n/a

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.