am currently setting up a fantasy football league.
i have set up the league, with team name overall points and position, my problem is that when 2 teams have the same points one team is omitted and the other team has its name on both entries
the formula's i am using are
vlookup = to find the name of team for the points
large = to find out points of each team
What I am trying to do is give the rank in column D based on the values in columns B and C. Some of the values in column B will have then same rank, and as such I want to add further criteria on which to rank them. I would first like to rank the values in column B and then rank the values in column C, which should give the rank in column D. For example Dog and Frog have the same value of 400 from the Non UK column. Therefore, rather than having these as both rank 1, I want them to be ranks 1 and 2, so want to add another criteria (UK). As Dog is greater than Frog in the UK (i.e. 10>7), I would like to rank Dog as 1 and Frog as 2. Goat will be ranked as 3 because it had the thrid highest value in the Non UK.
ABCD
1Non UKUKRank
2Cat20055
3Dog400101
4Eel200114
5Frog40072
6Goat30023
I'm having trouble with a small vba macro. At the end of the macro I test to see if two variables are equal and then print out true or false. However, for some reason even though the variables are equal vba is not treating them that way. I have put the values that represent the variables on a spreadsheet and used the if(x1=x2) formula and it says it is true, also, when I debug the macro and watch the values when it comes to test the logical expression the numbers are the same. I don't understand why vba does not say that the two variables are equal. I have attached a screenshot of the breakpoint where I double check the values are equal.
View 2 Replies View RelatedOne aspect of my Excel-based project involves comparing the operator-entered part number (in Excel) to a predetermined list of part numbers in one column of an Access database table. Right now, my program is telling me that every part number entered in the spreadsheet (50+) does not match any part number in the database, which I've verified to be incorrect. I've checked that both the spreadsheet part number and the database part number are of the string datatype.
To the best of my knowledge, my looping logic seems valid and robust. To the best of my knowledge, there are no hidden characters in either the database cells or in the spreadsheet cells causing this apparent mismatch. I'm completely stumped at this point as to why my program doesn't detect any matches between the spreadsheet and the database table. Below is the Sub containing the code for checking that the part numbers match:
[Code] ....
This issue seems to be a hybrid issue between Excel and Access with (to me) more of the issue on the Access end.
Trying to find the sum of all cells in the array described in the formula that are equal to the values inside the quotations. I used this exact (as far as I can tell) formula to find the sum of values that were NOT equal to my quoted values and it worked just fine. Any ideas why formula 'A' will not work but formula 'B' does work? I have a feeling I'm missing something simple here!
Formula A - Does not work:
=SUMPRODUCT(--('Master Lead Sheet'!$J$2:$J$10000=$B2),--('Master Lead Sheet'!$N$2:$N$10000="REJECTED"),--('Master Lead Sheet'!$N$2:$N$10000="CONDITIONED"),--('Master Lead Sheet'!$N$2:$N$10000="APPROVED"))
Formla B - Works:
=SUMPRODUCT(--('Master Lead Sheet'!$J$2:$J$10000=$B2),--('Master Lead Sheet'!$N$2:$N$10000"No Answer"),--('Master Lead Sheet'!$N$2:$N$10000"Disconnected"),--('Master Lead Sheet'!$N$2:$N$10000"Wrong Number"),--('Master Lead Sheet'!$N$2:$N$10000"EMAILED"),--('Master Lead Sheet'!$N$2:$N$10000"needs to be emailed"),--('Master Lead Sheet'!$N$2:$N$10000"Refund"),--('Master Lead Sheet'!$N$2:$N$10000"REFUNDED"))
I have 10 sales figures in C2:C11.
In D2:D11, I want the rank of those values, with one twist.
If the value in B2:B11 is X, then that value is excluded from the ranking.
I am trying to basically do:
=RANK(C2,IF(B2:B11="X",0,C2:C11))
Which formula can I use to rank the people below.
Consider that the lower the value on attendance and cancellations is, the better this number will be.
In the other cells the highest the value, the better the number is.
NameAttendanceUtilizationProductivityQualityCancellationsCharles089.38%26.60NA0Nick587.69%56.921001Brian384.51%51.32800Joe080.71%36.151000
Using formula, how do I rank
CAM100-24
COM98-21
CAM100-18
CAM27-37
COM121-5
COM98-23
so it first sorts in alphabetical order using the string before the "-" i.e. CAM27 comes before CAM100, and then by the biggest 'secondary values' (the value after the "-") so that COM98-23 comes before COM98-21?
I have a list of values (some are duplicates) which I need to rank.
The problem arises where I have more than one of any rank, the next rank skips a number. I need to have a list of sequential numbers and therefore can't have any numbers missing.
Example:
Value Rank Desired Rank
120 1 1
125 2 2
130 3 3
130 3 3
142 5 4
Got a question about RANK. As you can see from the example below, I want the rank of 1 to be 6, not 7 - same values keep the same rank but the next value takes the next rank number.
Is there a way to achieve this - I need to rank around 4000 lines...
Anyone know of a function that will return a rank of cell C3, in range C3:C800 that does contain a few #N/A values?
View 9 Replies View RelatedIn a range of 27000 records I dlike to find the lowest 5 values excluding, div0, N/A and other error things like that
View 9 Replies View RelatedI have two sheets of data and would like to rank according to the order in which the data fit on both sheets. Rank function only seems to allow one sheet at a time.
View 9 Replies View RelatedAttached is an excel file that contains all possible values in the first column (9238 of them) and a comma separated list of a subset of those values in the second column. I need to find a way to replace the smallest value with 1, the next smallest value with 2, ... , and the largest value with 9238 in every instance. In other words, I need their rank value.
OhioBlockGroup.xlsm
I'm using a Large function embedded in a Match function to a ranking.
The issue I'm having is that if there are 2 of the same values in the Top 5, it counts the first one twice rather than each one once.
I'm trying to rank the values in row 94 while ignoring only zero values and including positive and negative values. I'd like the function to rank the values in descending order, beginning with the most positive value and ending with the most negative value while ignoring all zeros. The function I've come up with so far ranks the positive values correctly and ignores the zeros, but the negative values are ranked as if they come after all of the zero values instead of being ranked immediately after the smallest positive value. Is there a way to fix the function below to make it do what I want?
=IF(D94=0,"",RANK(D94,$D$94:$AY$94,0))
I am creating a Power Poll Spreadsheet for a Fantasy Football League, and I need to create a code that will take the win/loss columns and in a new column assign rankings so that the team with the most wins gets ranked '1st', then '2nd' etc. I'd like it to, in the case of a tie, rank both teams equally 'the 3rd and 4th best teams both have identical records, so both get ranked '3rd' and the '4th' ranking gets skipped.
I have searched for a formula to allow this, and I can't find it. Can anyone help? As an aside, is anyone aware of a place I can find a listing of formulas that can be used in sports ranking, etc?
So, I have some names and values. Rank function give me order for those values.
Small function gives me ascending order. I want to get first n (let say 5) values back next to each other but can't use VLOOKUP function because sometimes I get duplicates (red numbers).
If there is more same numbersthat small function returns... It need to give me all of them, no matter 5 is limit. how to get back values of rank function that are duplicated. Book1.xls
I'm having problem with ranking results (ascending) with similar values e.g. values 1, 2, 2 and 4 fails by use of Rank-formula (rank: 1, 2, #N/A and 4) as value 2 appears twice.
I've attached a simple example of the problem.
I've created the following function that chooses the maximum value from a set of cells then inserts theappropriate row number (within a table) into a new cell.
=IF(J27=0,"?",IF(J27=J19,1,IF(J27=J20,2,IF(J27=J21,3,IF(J27=J22,4,IF(J27=J23,5,IF(J27=J24,6,IF(J27=J 25,7,IF(J27=J26,8)))))))))
It's working fine until I have 2 cells with the highest value. The above statement is entering the first cell that meets the criteria in the new cell but ignores the fact there maybe 2 (or more) of the same value.
How can I get both (or all) to be entered in the same cell? Is there a better way, maybe highlighting all the rows in the table that equal the max figure?
I have a spreadsheet that has a number of values and a separate sheet that has a paid total. What would be the easiest way to find the values in sheet 1 that total the sum in sheet 2? There may be more than one possible solution as to the items that total the sum.
View 1 Replies View RelatedI'm trying to sum all values greater than 5,000,00 and less than or equal to 10,000,000. I have this formula:
Code:
=SUMIFS(B6:B1359,B6:B1359,">"&L2,B6:B1359,"=
how to set all values in an array to be the same value. Currently I am using code in a For/Next loop like the following to do this:
Sub testing()
Dim ArrayToAllHaveSameValues(1 To 30) As String, i As Integer
For i = 1 To 30
ArrayToAllHaveSameValues(i) = "Value"
Next i
End Sub
I have a table full of phone numbers and call durations.
In A2:A6160, I have 162 different phone numbers and in U2:U6160, I have the call durations for calls made to those numbers.
In a separate spreadsheet, I have each number listed in Column A and in Column D, I want to display the average call duration for each of the numbers.
How can I count the number of cells in a range that contain values that are greater than or equal to zero?
View 2 Replies View RelatedI have a workbook with 2 sheets - [Names] has with a list of Staff Ids column B, [Access] has a list of their system acces - with their staff ids in column A and access in F (There could be more than 1 type of access),
I am looking to create a code that :
For each ID in [Names] B lookup in [Access] A
if found create a string with value in F ( so I have a list of all access )
And paste into F in [Names]
I have tried all sorts of things and just can't get it working.
1. I would like to be able to select a month from a drop down ( cell C4), and for Column B ('Cumulative Performance') to reflect the sum for each name between Jan and the month selected.
2. In Column D I would like to rank the relative position of the sum total; such that if I selected 'Dec', John would display '13' in D7, Anne '3' etc.
3. In Column E I would like to show by way of a coloured arrow (or even a smilie icon) the relative change in ranking of the sum totals evaluated for my chosen month with those calculated up until the previous month (e.g. for Anne, if I select June, the Jan to June total is 36 (rank 2 in the June total's), the May to Jan total for Anne is 32 (rank 1), therefore her relative rank movement between the June and May cumulatives moves down and cell E8 would show a red-down arrow (amber horizontal for no change and green up-arrow for an improvement in rank).
Is it possible to separate a range of cells with numerical values into "n" groups that have equal (or as close to equal as possible) total sums?
Ex. The range A1:A30 includes 30 random numbers between 1 and 12 (obviously there are duplicates). I need excel to auto-generate 6 groups of 5 cells each with each group having the same (again, as close as possible) total sum. Preferably, I'd like the numbers in each group to be similar from top to bottom, but I'm not overly concerned about that.
I need to group all records where the values in column A are equal. BUT I needed them sorted by another column (the whole sheet, not just within each group). Currently, all of the values in column A are listed in random order. Is there any way to do this manually?
to elaborate a bit further, consider the following:
Columns: A-B-C-etc.
1-Z...
1-X...
4-X
3-Y
4-Y
2-Z
Basically, I need to group all of the "1"'s and all of the "4"'s so that the extra values will show underneath the other. HOWEVER, I need them to be sorted such that the order in which they appear are XYZ. So the goal would be:
1-X
1-Z
4-X
4-Y
3-Y
2-Z
I have cells that they're values should be equal B6=B11, B7=B12, B8=B13, E6=B14, E7=B15, E8=B16, I want a macro that if the values are not equal then the background should be RED and a pop up message "The values should be equal".
View 9 Replies View Related