[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

 [Solved] Sort a column whose values are created by formula Goto page 1, 2  Next
Author Message
thesneens2
General User

Joined: 20 May 2012
Posts: 12

 Posted: Sun May 20, 2012 12:36 pm    Post subject: [Solved] Sort a column whose values are created by formula 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
General User

Joined: 05 Apr 2012
Posts: 33
Location: United States

 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

 Posted: Tue May 22, 2012 5:30 am    Post subject: HOW CAN I SORT A COLUMN WHOSE VALUES ARE CREATED BY FORMULA 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: 2374

 Posted: Tue May 22, 2012 7:58 am    Post subject: 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

 Posted: Tue May 22, 2012 9:33 am    Post subject: How can I sort a column Whose values are created by a formul 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.
thomasjk
Super User

Joined: 16 Dec 2005
Posts: 2374

 Posted: Tue May 22, 2012 10:21 am    Post subject: 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

 Posted: Tue May 22, 2012 11:24 am    Post subject: How to sort cells devived from a formula 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
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 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 https://forum.openoffice.org
thesneens2
General User

Joined: 20 May 2012
Posts: 12

 Posted: Tue May 22, 2012 1:20 pm    Post subject: How to sort cells derived from formulas `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: 1041

 Posted: Tue May 22, 2012 2:15 pm    Post subject: 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

 Posted: Tue May 22, 2012 3:46 pm    Post subject: HOW TO SORT COLUMNS WITH DERIVED CELL CONTENT Ed- I downloaded and installed 3.4 and the problem persists.
thesneens2
General User

Joined: 20 May 2012
Posts: 12

 Posted: Tue May 22, 2012 4:46 pm    Post subject: How can I sort a column of data derived from a formulaq 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. 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
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 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 https://forum.openoffice.org
thesneens2
General User

Joined: 20 May 2012
Posts: 12

 Posted: Wed May 23, 2012 7:26 am    Post subject: Sortilng columns containing formula derived values 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

 Posted: Wed May 23, 2012 2:14 pm    Post subject: Sorting columns contained values derived from formulas 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?
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 HoursGoto page 1, 2  Next Page 1 of 2

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum