I have a speadsheet that has a table containing scores of audits, in the last 3 columns of the tabe I have the two totals (columns AC and AD)
In Column "AF" I wish to show where they will rank based on the results, first I need to rank via the result of column AD and if that is the same as another result use column AC to determine where they rank.
I am trying to calculate players ranking based on their weight categories and attempts total, e.g. Category (50kg, 69kg etc..) Rank should be calculated automatically the highest total value of cat. 50kg is 1st second highest is 2nd etc. However, if tow players or more within the same Category have scored same point ranking will be calculated based on Body Weight less comes first and if both have similar weight calculation will be based on Start # first player comes first etc..
Table:
Start #
Name
Body Weight
Team
Category
Total Point
Rank
1
Player #1
49.2
Team #1
50kg
75
i would like to rank one table based on two columns, for example world cup group table to be ranked based on points but if two teams have the same no of points, then use goals scored column to break tie between them.
View 9 Replies View RelatedI have data in a number of columns in Excel which I want to sort on column B, then C, (Then D) however the formula I have entered is sorting based on column C below in ascending rather than descending order, how I can change this
=RANK(C2,$C$2:$C$4)+SUMPRODUCT(--($E$2:$E$4
i m creating a formula that will automatically place players in the correct position based on the outcome and there rank.
I have created an example sheet and the results should be in cells E3:E15
I have in column A a list of investment fund names. In columns B to D is respectively for each fund its Market Value, the Rate of Return and the Rank (1, 2, 3 or 4). I am trying to create a formula that will calculate the market value-weighted rate of return for each ranking.
For example, the 3 funds that rank first have market value weights of 10%, 12% and 13%, and rates of return of 1%, -3% and 5%. The total market-value weighted return for the top-ranked funds would therefore be 0.39% (i.e. 0.10*0.01-0.12*0.03+0.13*0.05)
The formula would first need to look in the ranking column to identify those funds with a particular rank (column D), then calculate their weights by dividing their market value by the total market value of all funds (column B), and finally multiplying these weights by the rate of return (column C), before summing the results to say, for example, that the total market-value weighted return for the top-ranked funds is 0.39%
how to set one entire columns text to two different colors based on another columns values. So for example I have column A and B. Column A has two values called Internal and External. Column B is a title table so the entire column is just titles. We'll say it goes for 20 rows if you need a row count. What I am looking to do change the text in Column B to Red for External and Blue for Internal. I tried the conditional formatting and I just can't seem to find the right option.
I'm using Win 8.1, Office 2013.
Basically a cell might have a number with 3 decimal places
For Example 90.554, and i will format the cell two 2 decimal places. But when i use those cells which are formated to 2 decimal places in a formula it produces results based of all the decimal places in that cell.
Is there a way for the formula to only calculate the results based off of what is shown in the cell? . This is messing up our accounting area by alot of dollars since we sell many items.
I included an example of what i mean in this excel file which should clear up any confusion.
I am attempting to upgrade a scoring system in excel 2003 which I use to rank wins in a sports results grid and i have to amend a formula based on a new version. The current formula in the attached part-completed worksheet Draw9 of 1 to 9, which I wish to amend, is in cells AM42:53 one of which is:
=IF(AL44="","",RANK(AL44,AL44:AL53)&CHOOSE(AND(RANK(AL44,AL44:AL53)<>{11,12,13})*MIN(4,MOD(RANK(AL44 ,AL44:AL53),10))+1,"th","st","nd","rd","th"))
I have now added an extra column of data in cell AN44:53 under “LSD” and I would like to use this to improve the accuracy of the original ranking. In other words I wish to Rank teams using the “Wins” in column AL42:53 plus the “LSD” data in columns AN44:53 and this will ensure that where teams are tied on the same number of wins then the numbers in the “LSD” column will enable a clear ranking i.e. say 3 teams on 3 wins ranked 6th = will now be ranked 6th,7th & 8th.
What I am doing is taking the below info which is only a portion of what I am really using but I need to calculate who appears in the lowest 25% of the entire ranking but also if they were there for both months... is this possible cause I cant seem to figure this out?
View 9 Replies View RelatedI am trying to create a formula that is able to calculate an end result based on the cell contents of 3 columns, the results are predefined in 3 other columns, here D, E & F
For example:
Column A Column B Column C Column D Column E Column F Column G (Results)
Royal Mail 1 100 2.8 2.3 1.2
Royal Mail 2 100 2.9 2.4 1.3
Royal Mail 3 100 3.0 2.5 1.4
DPD 5 200 4.5 2.8 1.5
DPOST 1 100 1.2 3.2 1.7
I am trying to create a calculation that in Column G will work out, IF Column A=Royal Mail AND Column B=1 AND Column C<=100 THEN Answer is [@[Column F]], IF Column A=Royal Mail AND Column B=2 AND Column C<=100 THEN Answer is [@[Column F]], IF Column A=Royal Mail AND Column B=3 AND Column C<=100 THEN [@[Column F]], IF Column A=DPD AND Column B=5 AND Column C<=200 THEN [@[Column E]], IF Column A=DPOST AND Column B=1 AND Column C<=100 THEN [@[Column D]]
Here are an example of what I have tried, amongst many...
=IF(AND(AND([@[Default Post Postal Service]]="Royal Mail"),AND([@[Default Postal Format]]=1),AND([@Weight]<=100)),1,0) Everything is zero.
It's probably best if you open up the attached example spreadsheet that contains sheets "Before" and "After" otherwise it might be pretty difficult to understand what I'm talking about.
So I have this spreadsheet where I have part numbers in column B, status of the orders in Column I and lead times in Column M. What I want to achieve is to get of Mode (col N), Median (col O) and Average (Col P) values of each part number and each of its status using the lead times in Column M. If possible I would only like this on the top lines of each of these. I have done the formulas manually on sheet After so if you click on the formula you can see the range's it covers.
I did create a concatenation in column K (conc of column B and I) as helper column because in theory this might make things a little bit easier.
Now going into second part. I would also like to do something very similar but only using the part number and status "Finished" and get the values in columns T,U, and V on the top row of each part number. I made some blue boxes and arrows to kind of point out the idea.
The solution can be either formula based or macro, it really doesn't matter, however I will intent to use it within macro either way.
I have long series of data in 8 columns (B, F, J, N, R, V, Z, AD) starting at row 5 down to row 86404. I want to find the total median for each row, where a condition is met for that row. The condition is if number>10 and the number is located in row 3 for each column (i.e., B3 for column B). So, if B3, J3, R3 are all >10, then I want to find the median for all the numbers in the columns B,J,R.
I assume there is an array formula that could do this? I've tried a few solutions with no luck.
create a macro that will find and calculate based on the details of 4 columns on the same spreadsheet.
Basically, here is what I need to do:
1. Need to calculate how many QTY IN (Column F) and PALLETS IN (Column I) of a specific PART# (Column A) and LOT CODE (Column B) For example if enter a PART # and LOT CODE, it will calculate how many QTY IN and PALLET IN of that PART # and LOT CODE.
2. Need to do the same for QTY OUT (Column G) and PALLETS OUT (Column J) also.
3. Output should contain the PALLET # , QTY IN and PALLETS IN based on the PART # and LOT CODE search. I just want to simplify the search function in this spreadsheet because it is very cumbersome if you need to find how many items left for that PART # and LOT CODE.
Attached is the spreadsheet..
I am trying to rank via data in two columns to eliminate any tied rankings.
I need to use the data in columns "AW" & "AX" to compile the rankings.
The ranking needs to be calculated by the highest number in column "AW" being ranked #1 and the second highest ranked #2 etc etc......any ties as a result of this ranking need to be broken by the lowest number in column "AX", the lower number being the higher in value and thus being the higher ranked.
For example:
AW AX RANK
13 89 7
13 63 4
14 89 2
14 81 1
13 90 8
13 66 5
13 58 3
13 87 6
I have attached the spreadsheet that I am using.
Running a NFL playoff pool and just actually used a previous post to finally correct my Rank formula which was great.
Basically I want to rank based on the POINTS column, and then based on the WINS column, and then based on two other things. The final tiebreakers are as follows.
1. Most points
2. Most wins
3. Picking the Super Bowl game correctly
4. Closest to the total points of the Super Bowl game (everyone has to pick what they think the total score of the game will be).
This is the formula used to Rank Points and Wins:
=RANK(M8,$M$8:$M$190)+SUMPRODUCT(--($M$8:$M$190=$M8),--(N8<$N$8:$N$190))
Can I add IF Statements to RANK formulas?
I have a project that I am unable to figure out the best way how to accomplish. I have five players on our basketball that are having a contest. Each player took 25 shots. I need to rank each player's shots individually. For instance, Andrew made 24 and 23 (4th and 1st attempts respectively) so he should be 1st and 2nd while Curtis would get 3rd because he was next with 22 made shots on his last attempt.
I would like to rank the numbers multiple columns (columns B thru F) in column G (not shown but understood).
If this is not possible, is there a way to list ALL the numbers in column G using a formula (the number of students will continue to grow so I am unable to simply enter =B2 in cell G2 and restart with =C2 in cell G7 (after five entries) because the number of students will grow.
A
B
C
D
E
F
[Code]...
I have a spreadsheet with values as per below
I can rank the data based on descending values in Columns B & C using the formula in cell B2
=RANK(B2,$B$2:$B$11)+SUMPRODUCT(--($B$2:$B$11=B2),--($C$2:$C$11>C2))
What I trying to do is also rank the data based on the values in column D as an additional tie-breaker
RankScore 1Score 2Score 3
1412315
2402420
2402422
4402030
5392015
******** ******************** ************************************************************************>Microsoft Excel - Win_calc.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutQ21=
QRSTUVWX21TickerLowestLowest*NameBOALEHBEARSecond*priceSecond*name22AT*US95/BEAR10210095**23ARW*US50/LEH605070**24BSX*US65/LEH666567**25CBS*US53/BEAR/MS555453**HVOL8*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
This is my general template. I get prices from BOA, LEH AND BEAR on the four companies listed. Then I use the Min function to tell me the best price (I'm buying). Then I use some IF statements to tell me who's the "Lowest Name" (lowest price):
=IF($G2=$C2,"/"&$G$1,"")&IF($H2=$C2,"/"&$H$1,"")&IF($I2=$C2,"/"&$I$1,"")&IF($J2=$C2,"/"&$J$1,"")&IF($K2=$C2,"/"&$K$1,"")&IF($L2=C2,"/"&L$1,"")&IF($M2=$C2,"/"&$M$1,"")
It checks if a cell is = the lowest cell value, then returns the header. This formula refers to different columns than my displayed sheet because Im hiding so cells and I just copied this to another section of my sheet. There's probably a better way to do this but this is what I have do far. I use this for 50 company names and 5 or six dealer banks (BOA, LEH, etc)
I'd like column that gives me the Second Place price and one that gives me the associated dealer (BOA, LEH).
I have the following data (measured in 1/1000th of an inch)(I have rounded down in this example though!!!)
NameABCDEAGGReal RANK
AAAAA0.110.120.090.120.270.1422
BBBBB0.120.1140.080.1350.2610.1421
CCCCC0.350.170.170.160.110.1923
copied down to a minimum of 100 rows (names) The AGG is the sum(a:e/5)
When u use RANK(agg,agg1:agg100,1) It ties Names AAAA and BBBB as they both have AGG of 0.142. Problem is the rule book states ties are split by the smallest of the results for each competitor in columns A to E. In this example BBBB is 2nd as he has smallest result in Column C being 0.08.
I have tried the AGG+1/min(a:e) and still end up with incorrect results. The problem is the numbers are so small and you have to split ties all the way to the 100th person involved. I cant use sort either as the data has to be printed in alphabeticall name order all the way to 100 names with their ranks recorded against the names.
I have a customer spread sheet that uses a “Ranking” to establish the next “Contact Date” based on the “Ranking” and the “Last Contact Date”.
An example is – a “Customer” with an A “Ranking” would be contacted 21 days after the “Last Contact Date”, with a B “Ranking”, 28 days after the “Last Contact Date” etc.
Is there any way that the “Next Contact Date” can be automatically up dated when the “Ranking” is changed and there are multiple rankings (A, B,C, D etc)
I have a table that has multiple products and price points in it and I would like to add a field that ranks the products by price but for only against that product.
Product Price
Result needed is the Rank only by product based on Price
A
150
2
A
155
3
B
130
1
[Code] ........
Ranking values and ranking duplicates the same rank I'm looking to rank a group of numbers that will possibly have duplicate numbers that I want to show as a tie but have no break in the rankings.
Example:
20
18
18
15
12
11
11
9
The ranking I am looking for is:
1
2
2
3
4
5
5
6
I have a input selection, when i keyin XYZ, the data under column XYZ should be ranked in ascending order. If i select ABC then it should rank ABC data in ascending order.
XYZ
ABC
Input--->XYZ
XYZABC
15
38
26
42
Rank the column by selectionXYZABC
I'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))
I need a formula that looks at the percentage values of 3 cells and the in the next row ranks them 1st,2nd or 3rd. So for example cells A1, A2 and A3 have 30%, 20% and 10% respectively, so in cells B1, B2, and B3 I need to return 1st, 2nd, 3rd. But next week it could be that A1, A2, and A3's values are 20%, 30% and 10% respectively, and so I'd need cells B1, B2 and B3 to return 2, 1, 3, and so on. Be cool if the rows could automatically re-arrange so that whatever is ranked 1 is always on top, 2 in the middle and 3 at the bottom, but not that important. Be also cool if the 1st ranked row, chnaged colour or flashed or something, again not important though.
View 4 Replies View RelatedI am trying to sum the results of a filter via VBA but cannot seem to get it right. I have the code to count the number of "active" rows, now to get an average I have been trying to use similar code to get the sum so I can divide the sum by the number of rows not filtered out. I get the error "Unable to get the Sum property of the WorkSheet function class" This is the code I have butchered:
Set R = ActiveSheet. Range("d1"). CurrentRegion
Set R = R.Offset(1, 0).Resize(R.Rows.Count - 1, R.Columns.Count)
For i = 1 To R.Rows.Count
If Not R.Rows(i).EntireRow.Hidden Then
intCountCancelled = WorksheetFunction.Sum("Cancelled") ' cancelled is a named range. This is the line its failing on
End If
Next i
I have a list of data in Excel with Autofilters. At the bottom of the data set is a row that totals up many of the columns. Whenever I use the Autofilters, the totals at the bottom still reflect the entire set of data, rather than only the filtered data.
Is there a way (using macros, formulas, or something else) to make the totals automatically update to only reflect the data that is visible when using Autofilters?
How to do a ranking for productivity based on goals in six categories for my employees. I want to be able to rank them on these key categories and rank them based on the best average. For instance:
Employee #001 Ranked 1st in Sales per Hour, 3rd in Avg Transaction Amount, 2nd in Multiple Sales, 5th in Accessory Percentage
Employee #002 Ranked 2nd in Sales per Hour, 1st in Avg Transaction Amount, 1st in Multiple Sales, 4th in Accessory Percentage
I want to factor against the goals to determine each individual rank for each category and then rank based on the combination of them all.
I haven't gotten to all the additional conditional formatting but recall how to do this with ease, but the sheet in its basic format is present in the attachment below.
Employee Productivity.xls‎
How to return ranking based on students' score?
STUDENT SCORE Rank MAY 11255 4 JACK 5262 5 TIM 432525 1 STEVEN 352332 2 HUGO 32232 3 FERNANDO 5153 6 SANDY 4556 7 SARAH 2265 8