I want to be able to rank different IDs for a certain name based on $$$. For example, if person A has several IDs, I want to be able to tell the top 5 IDs based on $$$. Also, there are two problems. One is, there are several duplicate IDs and I want to rank "None" for those which already have rank. Second, there are different IDs with the same $$$, I want to be able to tie break those.
View 3 Replies
Here is an example of my data and desired results:
A B C
[Material] [Receipt Date] [Order Received (Desired Results)]
1 8/1/11 1
1 8/1/11 1
1 9/1/11 2
2 8/2/11 1
2 8/5/11 2
2 8/5/11 2
2 9/1/11 3
I would like to identify the order in which each "material" was received but I would like the ranks to be sequential.
I am aware of the:
=SUMPRODUCT(--($A$2:$A$8=$A2),--(E2>E$2:E$8))+1
to return the rank but I would like to make them sequential...
One last request... I would LIKE to not use array functions if possible, however I will if I have to..
I have a "RANKIF" formula using SUMPRODUCT:
=SUMPRODUCT(--($B4=$B$4:$B$100),--($F4
I have a word document with sentences which has to be broken down with length of 65 characters (words should not be broken). This has to be stored in consecutive cells.
Example
sample text input
This is a sample sentence format from a document which has to be broken down with length of 65. This is created by 'VENOM' on 22-09-2009 for sampling it into a excel sheet named as 'The sample.xls'.
Output
This is a sample sentence format from a document which has to be
broken down with length of 65. This is created by 'VENOM' on
22-09-2009 for sampling it into a excel sheet named as
'The sample.xls'.
REQ:
1. words should not break
2. words with special characters should not break(like.. 22-09-2009)
3. words in quotes has to come in full (like.. 'The sample.xls'.)
I'm tring to solve an issue with ranking with a tie breaker. I don't know how to explain it without sounding confusing so I'm attaching a sample, but basicly I want to be able to rank people with certain points and a set tie breaker value.
View 2 Replies View RelatedI've got 3 columns of info.. Name, Org, #Sales, $$..
I need to rank the list by:
Org, $$, #of Sales (as the tie breaker)
So, I need it to look for a specific Org Dept in Column B, rank the Dollar Amounts in column C, and if there are any ties, go to #Sales in column D and use the biggest number there to break the tie.
I've found 2 formulas..
This one was awesome for looking for the specifc Org Dept and only ranking those numbers that matched the designated Org Dept. (but this can't break any ties)..
IF(ISERROR(MATCH($O3,LARGE(IF($P$3:$P$53=R$2,$O$3:$O$53),ROW(INDIRECT("1:"&COUNTIF($P$3:$P$53,R$2)))),0)),"",MATCH($O3,LARGE(IF($P$3:$P$53=R$2,$O$3:$O$53),ROW(INDIRECT("1:"&COUNTIF($P$3:$P$53,R$2)))),0))
Then there's this one that broke ties, but can't recognize a specific Org Dept that I need..
=RANK($O3,$O$3:$O$53)+SUMPRODUCT(($O$3:$O$53=$O3)*($F$3:$F$53>$F3))
Basically it is another sports modelling experiment that I have had (I get the day off work and come up with things like this ), where I just had a curiosity to play out the last games for the top 3 teams in the English Premier League (just to see if I could get some percentages).
The attached, is just the values from one run of my model (I have taken the workings out to reduce the size and for other reasons).
Basically everything to the left of column K, is the model and I am happy with it (and I know goals have to be integers - this is basic);
This plays the games out, and feeds the data into the table at M36 (highlighted yellow), which feeds the data tables at M42 (highlighted green), which are counted to get percentages in the tables (highlighted yellow at M6).
I have put some example output from runs on the second worksheet (so you can see how it works - I like that it suggests Chelsea can win even though it won't be very likely )
Basically you can probably see the problem in cell O22. The percentage chance of finishing first should equal 100% when totalled (e.g. the sum of Liverpool, Chelsea and Man City finishing first, as it has to be one of them should equal 100%). I tracked this back to the fact that I had no way of splitting teams level on points. As a result I added Goal Difference to the model (crudely - e.g. decimal) and here's the question;
How would I go about using Goal Difference to break tie's effectively?
I am thinking I could do some kind of IF function in Q37 (and then have a tie-breaked rank in column R to feed the data tables), on the basis of if anything in N37 to N39 was equal but I am not sure how I would go about applying this.
Example Values_EPL.xlsx
