General Betting
Welcome to Live View – Take the tour to learn more
Start Tour
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!!!
Show More
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.
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.
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
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
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
Waiting on anyone else with a specific function or better formula
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 :(
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
=LARGE($B$1:$B$5,2) will, I believe, give the 2nd highest number.
From there you can do it in one line
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.
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.
Oh ...cool! I'll get used to that function
(I meant B1 etc in my redundant suggestion above)
=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
A1 being the cell. There is a greater than sign where it probably says A10 after I post this, should be A1greaterthan0
Please accept my thanks as a donation to the Lori 24hr Excel Helpdesk Benevolent Foundation
:^0
It's just a shame that I'm actually really crud at excel and have to wangle things to get them to work.
It's just a shame that I'm actually really crud at excel and have to wangle things to get them to work.
snap, and a couple of points below top rated
Question 2 formula ~
=RANK(B1,$B$1:$B$5,)+COUNTIF($B$1:B1,B1)-1
Just having a look at question 1
Cheers
=RANK(B1,$B$1:$B$5,)+COUNTIF($B$1:B1,B1)-1
Just having a look at question 1
Cheers
Typo error should be
=RANK(B1,$B$1:$B$5)+COUNTIF($B$1:B1,B1)-1
=RANK(B1,$B$1:$B$5)+COUNTIF($B$1:B1,B1)-1
For Question 1, I can't improve on Lori's formula
Cheers
Cheers
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
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
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
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
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
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
Post Your Reply
Please login to post a reply.
Wonder
Instance ID: 13539