| View previous topic :: View next topic |
| 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
|
|
|
| Back to top |
|
 |
David
Super User
Joined: 24 Oct 2003
Posts: 5668
Location: Canada
|
| 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. |
|
| Back to top |
|
|
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? |
|
| Back to top |
|
|
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 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 |
|
|
David
Super User
Joined: 24 Oct 2003
Posts: 5668
Location: Canada
|
| 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. |
|
| Back to top |
|
|
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 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 |
|
|
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 |
|
| Back to top |
|
|
|
|
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
|
