General Betting
There is currently 1 person viewing this thread.
12 Feb 10
08:22
Joined:
Date Joined:
03 Sep 08
and girls of course.
1. I want columns to show difference from MAX, for ratings, odds, weights etc.
E.g 140 136 137 138 129 will return 0,4,3,2,11
This step is easy =(MAX($A$1:$a$5)-CELL)
But what if I want to show the points clear of second-rated for the top-rated?? In the example above I would want a column returning -2,4,3,2,11
I dont mind using mutiple columns to achieve this if unavoidable.
2. How can I default all my rankings to avoid joint positions, and to settle joint positions in race card order.
e.g column A is card number 1,2,3,4,5
Column B is weight 126 126 126 123 123
How would I get a rank of 1,2,3,4,5 instead of 1,1,1,4,4,??
THANKS!!!
1. I want columns to show difference from MAX, for ratings, odds, weights etc.
E.g 140 136 137 138 129 will return 0,4,3,2,11
This step is easy =(MAX($A$1:$a$5)-CELL)
But what if I want to show the points clear of second-rated for the top-rated?? In the example above I would want a column returning -2,4,3,2,11
I dont mind using mutiple columns to achieve this if unavoidable.
2. How can I default all my rankings to avoid joint positions, and to settle joint positions in race card order.
e.g column A is card number 1,2,3,4,5
Column B is weight 126 126 126 123 123
How would I get a rank of 1,2,3,4,5 instead of 1,1,1,4,4,??
THANKS!!!
sort by:
Show
per page
Replies: 21
|
By:
There will be a better solution than this, but for 2 you could add ((50-racecard number)/10000) to each rating before finding the max... format the cells to avoid the 0.000x, but it will still exist.
|
|
By:
I hoped you'd be around Lori,i think i'm pretty good at ideas like that but you beat me hands down. Yeah there's gonna be a more formal way to do it but I get that one.
|
|
By:
I can think of nothing more creative for the first question than two columns, the first one having what you said... =(MAX($A$1:$a$5)-CELL) in column B
The second one being column C =IF(B1 is greater than 0,B1, 0-MIN($B$1:$B$5)) (No greater than signs work on this forum. That would mean If B1 is not 0, then returns B1, otherwise returns 1 minus the lowest number in column B (Which is the only negative one) I am certain that this one isn't the best solution and also that you've probably already thought of it, but at least it's a starting point |
|
By:
I had a method to identify second-rated and take it from there but I'd have needed at least 2 more columns to do it. Thanks!
Waiting on anyone else with a specific function or better formula |
|
By:
Mine doesnt work sorry, I was reading the -2,x,x,x,x part of your post. Getting the -2 is of course the tricky bit :(
|
|
By:
Ok I found it, you need to use LARGE not max
=LARGE($B$1:$B$5,2) will, I believe, give the 2nd highest number. From there you can do it in one line |
|
By:
I'm along the lines of IF A1 is 0 return 0,IF A1 is greater than 0 return 1.
If you multiply this by the original rating you knock out top-rated and MAX gives you the second-rated. I can then get the difference between top and second, but it will be in a sepaarte column. Maybe thats unavoidable and I can still use it. |
|
By:
Oh ...cool! I'll get used to that function
|
|
By:
(I meant B1 etc in my redundant suggestion above)
|
|
By:
=IF(MAX($A$1:$A$5)-A10,(MAX($A$1:$A$5)-A1),LARGE($A$1:$A$5,2)-A1)
A1 being the cell. There is a greater than sign where it probably says A10 after I post this, should be A1greaterthan0 |
|
By:
(I've tested that one, it works!)
|
|
By:
Please accept my thanks as a donation to the Lori 24hr Excel Helpdesk Benevolent Foundation
|
|
By:
:^0
It's just a shame that I'm actually really crud at excel and have to wangle things to get them to work. |
|
By:
snap, and a couple of points below top rated
|
|
By:
Question 2 formula ~
=RANK(B1,$B$1:$B$5,)+COUNTIF($B$1:B1,B1)-1 Just having a look at question 1 Cheers |
|
By:
Typo error should be
=RANK(B1,$B$1:$B$5)+COUNTIF($B$1:B1,B1)-1 |
|
By:
For Question 1, I can't improve on Lori's formula
Cheers |
|
By:
guys, sorry for hijacking the thread but I have a similar problem with my ratings.
All the ratings are downloaded into each race meeting but they are not in order, ir highest to lowest. What I am currently doing is highlighting each race and using sort. Is there a quicker way of going from this Drumpellier 84 66 85 85 Eight Hours 82 88 84 88 Maoi Chinn Tire 84 82 84 84 Paint By Numbers 74 64 64 74 Sandy Toes 78 76 84 84 Singingintherain 80 84 82 84 to this Eight Hours 82 88 84 88 Drumpellier 84 66 85 85 Maoi Chinn Tire 84 82 84 84 Singingintherain 80 84 82 84 Sandy Toes 78 76 84 84 Paint By Numbers 74 64 64 74 Thanks |
|
By:
If the first line of data is in cell A1
in a blank column enter this formula in the same row =RIGHT(A1,2) fill down as needed Highlight all the data in column A and the column with the formula and then do a sort by the column with the formula in it, sort by number highest to lowest |
|
By:
Compound Magic .. thanks for your suggestion! I'll try to run through it later. Very glad to have got very workable solutions to my post.
Gelding, hope that works for you. If you ever want any input on how you are sorting out and using your data you can contact me at simonsimon141 at hot mail etc |
|
By:
Cheers guys much appreciated.
|