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

Author Message
ltsnotes
Newbie

Joined: 08 Nov 2005
Posts: 3

Posted: Tue Nov 08, 2005 4:04 pm    Post subject: Macro or Formula for OOo Calc

I have a spreadsheet that I want to use to score an event. I want to enter the time or score in column A and then have a macro that will rank the scores in column B starting at one. It also needs to be fairly dynamic, instead of giving it a column I want to place a button in the column an have the macro sort that column and put the results in the adjacent column. I have no clue how to do this, any help would be appreciated.
 Code: Score     Rank     5.27        3 2.33        2 1          1
David
Super User

Joined: 24 Oct 2003
Posts: 5668

Posted: Tue Nov 08, 2005 4:24 pm    Post subject: Re: Macro or Formula for OOo Calc

ltsnotes wrote:

 Code: Score     Rank     5.27        3 2.33        2 1          1

It sounds a bit like you want to run before you have yet to crawl? You can be ambitious and ask for the macro from a button, but why not settle for a nested IF() statement? You have not made clear the limitations for scoring, except to give some example, so I'll guess that you want a rank of "3" if the score is between 4 and 6, of "2" if between 2 and 4, and otherwise "1".

That can be done with scores indicated in cells A1, A2, A3, and if you put =IF(A1>4;3;IF(A1>2;2;1)) into cell B1 then copy down column B. There are other ways to do the comparison, but all essentially the same in the end result. If you don't want a column, then just use the cells A1 and B1, and enter a value in A1 to get the score in B1. You can pretty up the appearance if that matters.

David.
ltsnotes
Newbie

Joined: 08 Nov 2005
Posts: 3

 Posted: Tue Nov 08, 2005 4:34 pm    Post subject: Re: Macro or Formula for OOo Calc Thanks for the quick reply. Actually I want to sort the first column then order them in the second column. For example if you had scores of 5, 2.5 , 3.1, 6 ,8 , 1 in cells from A1 - A6 then in B1 - B6 I want the corresponding order: 4, 2, 3, 5, 6, 1 . All Scores get ranked and the team with the lowest number for each event (the most 1's) wins that event. Does my explanation make sense?
Dale
Super User

Joined: 21 Feb 2005
Posts: 1440
Location: Australia

 Posted: Tue Nov 08, 2005 5:55 pm    Post subject: David's suggestion for nested IF() statements will work. I would like to suggest an alternative approach. Assumptions:Your data (scores) in column A Headings in Row 1 Rank in column BIn B2 type =RANK(A2;\$A\$2:\$A\$xxx;1) and fill down (where xxx is the bottom of you data range) (see help for FILL). To understand what the formula is doing browse Help for RANK. You can then use the COUNTIF() (or even simpler, the SUMIF()) function to show the number of 1s each team has acheived. Edit SUMIF was SUM._________________Dale To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me! OOo documentation from the source http://documentation.openoffice.org Guides, FAQ, How Tos
David
Super User

Joined: 24 Oct 2003
Posts: 5668

Posted: Tue Nov 08, 2005 8:25 pm    Post subject: Re: Macro or Formula for OOo Calc

 ltsnotes wrote: For example if you had scores of 5, 2.5 , 3.1, 6 ,8 , 1 in cells from A1 - A6 then in B1 - B6 I want the corresponding order: 4, 2, 3, 5, 6, 1 .

Dale's approach using RANK() is the correct one.

David.
ltsnotes
Newbie

Joined: 08 Nov 2005
Posts: 3

Posted: Tue Nov 08, 2005 9:53 pm    Post subject:

 Dale wrote: David's suggestion for nested IF() statements will work. I would like to suggest an alternative approach. Assumptions:Your data (scores) in column A Headings in Row 1 Rank in column BIn B2 type =RANK(A2;\$A\$2:\$A\$xxx;1) and fill down (where xxx is the bottom of you data range) (see help for FILL). To understand what the formula is doing browse Help for RANK. You can then use the COUNTIF() (or even simpler, the SUMIF()) function to show the number of 1s each team has acheived. Edit SUMIF was SUM.

Thank you !!
Oh thats perfect ! and simple too. One minor thing, how can I make the B column cell blank if A has no value? Now it says #VALUE! until I put a score in column A
Dale
Super User

Joined: 21 Feb 2005
Posts: 1440
Location: Australia

Posted: Wed Nov 09, 2005 2:09 pm    Post subject:

 ltsnotes wrote: ...how can I make the B column cell blank if A has no value? Now it says #VALUE! until I put a score in column A
=IF(A2="";"";RANK(<as earlier>))
If that doesn't work as written, play around with ISNA, ISBLANK, ISNUMBER, ISERROR functions in place of A2="" - browse help for information; functions

You're welcome.
_________________
Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!

OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 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