OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[Solved] Sort a column whose values are created by formula
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
thesneens2
General User
General User


Joined: 20 May 2012
Posts: 12

PostPosted: Sun May 20, 2012 12:36 pm    Post subject: [Solved] Sort a column whose values are created by formula Reply with quote

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.
Back to top
View user's profile Send private message
UmTheMuse
General User
General User


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

PostPosted: Mon May 21, 2012 3:14 pm    Post subject: Reply with quote

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).
Back to top
View user's profile Send private message
thesneens2
General User
General User


Joined: 20 May 2012
Posts: 12

PostPosted: Tue May 22, 2012 5:30 am    Post subject: HOW CAN I SORT A COLUMN WHOSE VALUES ARE CREATED BY FORMULA Reply with quote

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
Back to top
View user's profile Send private message
thomasjk
Super User
Super User


Joined: 16 Dec 2005
Posts: 2374

PostPosted: Tue May 22, 2012 7:58 am    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
thesneens2
General User
General User


Joined: 20 May 2012
Posts: 12

PostPosted: Tue May 22, 2012 9:33 am    Post subject: How can I sort a column Whose values are created by a formul Reply with quote

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
Back to top
View user's profile Send private message
thomasjk
Super User
Super User


Joined: 16 Dec 2005
Posts: 2374

PostPosted: Tue May 22, 2012 10:21 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
thesneens2
General User
General User


Joined: 20 May 2012
Posts: 12

PostPosted: Tue May 22, 2012 11:24 am    Post subject: How to sort cells devived from a formula Reply with quote

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?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue May 22, 2012 11:45 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
thesneens2
General User
General User


Joined: 20 May 2012
Posts: 12

PostPosted: Tue May 22, 2012 1:20 pm    Post subject: How to sort cells derived from formulas Reply with quote

`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.
Back to top
View user's profile Send private message
Ed
Super User
Super User


Joined: 28 May 2003
Posts: 1041

PostPosted: Tue May 22, 2012 2:15 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
thesneens2
General User
General User


Joined: 20 May 2012
Posts: 12

PostPosted: Tue May 22, 2012 3:46 pm    Post subject: HOW TO SORT COLUMNS WITH DERIVED CELL CONTENT Reply with quote

Ed-
I downloaded and installed 3.4 and the problem persists.
Back to top
View user's profile Send private message
thesneens2
General User
General User


Joined: 20 May 2012
Posts: 12

PostPosted: Tue May 22, 2012 4:46 pm    Post subject: How can I sort a column of data derived from a formulaq Reply with quote

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.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed May 23, 2012 3:40 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
thesneens2
General User
General User


Joined: 20 May 2012
Posts: 12

PostPosted: Wed May 23, 2012 7:26 am    Post subject: Sortilng columns containing formula derived values Reply with quote

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?
Back to top
View user's profile Send private message
thesneens2
General User
General User


Joined: 20 May 2012
Posts: 12

PostPosted: Wed May 23, 2012 2:14 pm    Post subject: Sorting columns contained values derived from formulas Reply with quote

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?
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group