[Solved] Sort a column whose values are created by formula

thesneens2 · General User Joined: 20 May 2012 Posts: 12

My spreadsheet is designed to record weekly golf scores and to create an average score for each player. That part of the spreadsheet works but I can't sort the 'average' column to array the players by average score. Any thoughts? My [/b]formula to create the average is very straightforward--it identifies each cell that could be used and is very long.

UmTheMuse · Posted: Mon May 21, 2012 3:14 pm Post subject:

Are you trying to write your own sort and average functions? What's wrong with the ones that Calc already has?

In case you didn't know, the sort tool is under Data. Average is a normal function (ie. type =AVERAGE(A1:B5) to get an average for all cells between A1 and B5, inclusive).

thesneens2 · General User Joined: 20 May 2012 Posts: 12

I am trying to use the sort function under data. It works on columns containing data which has been manually entered, but does not work for those columns whose values were derived via formula. I've processed this application on the spreadsheet associated with works with no problem. Am I missing a step somewhere?
My average formula is, for example, =average(a1:a50) and the computation is correct.
Help

Removed all caps - floris v, moderator

thomasjk · Super User Joined: 16 Dec 2005 Posts: 2308

Turn off the Caps Lock and stop shouting. We can read just fine with out all CAPS.

Does the range have column labels? If not on the Options Tab uncheck this box. IF so make sure it is checked. Sorting works as expected for me unless the option I mentioned is checked when there are no column labels.

What Version of OOo and OS do you use?

thesneens2 · General User Joined: 20 May 2012 Posts: 12

Sorry about the Caps. I have tried sorting with and without a header option indicated and with and without a header in the range--nothing happens. I have Windows 7 but don't know what open office I have--got it about a year ago and this is my first use of calc. Confused

thomasjk · Super User Joined: 16 Dec 2005 Posts: 2308

Try resetting the user profile http://user.services.openoffice.org/en/forum/viewtopic.php?f=74&t=12426 . With OOo open Click Help-->About and post the result here.

thesneens2 · General User Joined: 20 May 2012 Posts: 12

Thanks for the suggestion, but I'm not a PC sophisticate. I have Windows 7, do not have windows explorer, and the internet explorer I do have does not have organize and tools does not have folder options. I have not customized anything--can I simply install a new copy of open office?

Villeroy · Posted: Tue May 22, 2012 11:45 am Post subject:

A forum can not really help in this case.
This is the year 2012. Go and visit some computer course.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

thesneens2 · General User Joined: 20 May 2012 Posts: 12

`thomasjk-I found the user file and renamed it as suggested. However, the file I have is OpenOffice.org2. Perhaps I have an outdated version of the package. When I opened my application there was no indication the profile was being changed. Fuirther suggestions?
Thanks for the interest.

Ed · Super User Joined: 28 May 2003 Posts: 1040

What version of OOo have you got? You can find out from "About OpenOffice.org" in the "Help" menu.

If it is 2.something then it is several versions out of date, the latest is 3.4. I would advise getting the latest version from http://www.openoffice.org/ and see whetner your problem still exists.

thesneens2 · General User Joined: 20 May 2012 Posts: 12

Ed-
I downloaded and installed 3.4 and the problem persists.

thesneens2 · General User Joined: 20 May 2012 Posts: 12

I have resolved my problem.

I didn't realize that when sorting columns whose content is derived from a formula the defined range in the sort function must include the data cells used in any formula. Very Happy

The help section related to defining ranges would be a good place for this caveat.The sort works fine once the range is properly defined. If the range is not lproperly defined the sort function simply does not work and no error message is generated.

Thanks to those of you who offered help with this problem.

Villeroy · Posted: Wed May 23, 2012 3:40 am Post subject:

The issue is still not reproducible by anyone. One thing I can reproduce is the difference between =AVERAGE(A1:B5) and =AVERAGE($A$1:$B$5) when sorting.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

thesneens2 · General User Joined: 20 May 2012 Posts: 12

Does anyone else out there get a non-result when trying to sort a column of formula derived data when the range definition for the sort does not include the cells in the formula?

thesneens2 · General User Joined: 20 May 2012 Posts: 12

Villeroy-
I replicated the problem/issue on another laptop which had 3.3 installed. What is your point in mentioning a different format for the average function which produces a different result?