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

Macro or Formula for OOo Calc

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
ltsnotes
Newbie
Newbie


Joined: 08 Nov 2005
Posts: 3

PostPosted: Tue Nov 08, 2005 4:04 pm    Post subject: Macro or Formula for OOo Calc Reply with quote

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


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Tue Nov 08, 2005 4:24 pm    Post subject: Re: Macro or Formula for OOo Calc Reply with quote

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


Joined: 08 Nov 2005
Posts: 3

PostPosted: Tue Nov 08, 2005 4:34 pm    Post subject: Re: Macro or Formula for OOo Calc Reply with quote

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


Joined: 21 Feb 2005
Posts: 1440
Location: Australia

PostPosted: Tue Nov 08, 2005 5:55 pm    Post subject: Reply with quote

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


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Tue Nov 08, 2005 8:25 pm    Post subject: Re: Macro or Formula for OOo Calc Reply with quote

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


Joined: 08 Nov 2005
Posts: 3

PostPosted: Tue Nov 08, 2005 9:53 pm    Post subject: Reply with quote

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


Joined: 21 Feb 2005
Posts: 1440
Location: Australia

PostPosted: Wed Nov 09, 2005 2:09 pm    Post subject: Reply with quote

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
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
Page 1 of 1

 
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