i want my formula to look up the highest value from a range (the =max column), then return a name in the leftmost column. What it actually is, is a player of the month for fantasy football. Each week the player gets a score, then each month, a total of four weekly scores. i want to look up who scored the most and return the player name to me. see the attachment.
View 2 Replies
I've am array formula that I use to provide highest values:
Code:
{=INDEX(Data!$D$2:$D$128, MIN(IF(LARGE(Data!$D$2:$D$128,ROW(A1))
=Data!$D$2:$D$128, ROW(Data!$D$2:$D$128)-MIN(ROW(Data!$D$2:$D$128))+1)),
MATCH(LARGE(Data!$D$2:$D$128,ROW(A1)), INDEX(Data!$D$2:$D$128,
MIN(IF(LARGE(Data!$D$2:$D$128,ROW(A1))=Data!$D$2:$D$128, ROW(Data!$D$2:$D$128)-
MIN(ROW(Data!$D$2:$D$128))+1)), , 1), 0), 1)}
I want to replace the range Data!$D$2:$D$128 with a dynamic reference: like that one:
Code:
ADDRESS(MATCH(Summary!$B$1,Data!$Q$2:$Q$10000,0)+1,
MATCH(Summary!$B$7,Data!1:1,0),1,1,"Data")&":"&ADDRESS(
MATCH(Summary!$B$1,Data!$Q$2:$Q$10000,0)+COUNTIF(Data!$Q$2:$Q$10000,Summary!$B$1),
MATCH(Summary!$B$7,Data!1:1,0),1,1,"Data")
The two formulas work well in separate sheets but crash when put together, how could I possibly insert the second address formula into the first one?
I use the following CSA formula to give me the total highest intervals of zeros in a range. I understand how most of it works apart from one thing and that's how the data array and bins array give me the result.
=MAX(FREQUENCY(IF(H2:Y2=0,COLUMN(H2:Y2)),IF(H2:Y2>0,COLUMN(H2:Y2))))
So this is what I understand:
H2:Y2=0 - Returns TRUE/FALSE in the array based on range having 0 or a value other than that - ie{TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE}
COLUMN(H2:Y2) - Returns the column number {8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25}
IF(H2:Y2=0,COLUMN(H2:Y2)) -Returns the column number for every TRUE value
IF(H2:Y2>0,COLUMN(H2:Y2)) - This does the same as the above steps but returns column numbers for every cell that contains a value greater than 0
MAX - Returns the biggest number within the array ie =MAX({3;0;0;0;0;0;9}) would return 9
What I need to know is how the FREQUENCY formula gives {3;0;0;0;0;0;9}
I have a range of 30 cells in a row. From that row there are groups of cells that have data that might be 1-7 cells populated in a row and in between these groups are blank cells in the row. I need to see what is the highest number of cells in a continuous row that contain data, what the mode is for continuous cell counts, and what is the average value for the data from the continuous cells. The average is a nice to have but not entirely necessary as most cell data will be fairly similar and I can get a close enough average just by knowing the average number of cells that contain data in a row. I have to do this calculation for about 1000 rows of individual data so its important a formula fit in one row.
Almost need a count function to count starting with each cell and then stopping at the first no no value it finds. Then it would show those counts and I could run a formula to pick the highest count and also show the mode of counts.
I will let the experts at it!, my weak mind could not think this one through. I thought something to do with MATCH for a while but no go from what I could do.
I need a cell formula that will (a) identify the highest N values in an above specified column range, (b) color the interior of those N cells (I suspect that this is not possible), and most importantly (c) return the average value of N corresponding cells, where the corresponding cells are located on the same rows as the identified N high value cells but in a specified column to the left (not necessarily adjacent)
Does anybody know what this formula would look like?
Example:
-----------------------
...| A | B | C | D | E | F |
-------------------------
1 |....| * |.........| 7 |...
-------------------------
2 |....................| 2 |...
-------------------------
3 |....| * |.........| 6 |...
-------------------------
4 |....................| 1 |...
-------------------------
5 |....| * |.........| 5 |...
-------------------------
6 |....................| ? |...
-------------------------
? = average of B1,B3,B5 where (N = 3) and (specified column to the left = B)
I have a large spreadsheet of data that contains 3 columns: Columns are Name, Revision, # of items. I want to find the # of items for the highest revision for each name. List looks like this:
Smith, 1, 30
Smith, 2, 36
Smith, 3, 18
Johnson, 1, 125
Johnson, 2, 130
Lopez, 1, 8
Lopez, 2, 12
Lopez, 3, 15
etc…
I'm only interested in the data for the latest Revision. There's over 500 names. A lookup or pivot could be possible?
Basically I want to get the highest value in a cell from a range of cells on another sheet within the same spreadsheet......
The problem is that the values in the range of cells I want it to look up are not just straight values but rather the summary of two other cells as follows:-
=IF(BN5<"",BO5&" - "&BP5,"") which means that the Values in the cells that the formula needs to search will read something like:- "1250 - B Builder"
Also, cell BN5 is where the archers 3rd highest score gets entered and as such only at this point in time do they 'qualify' for the Standings Table Sheet.....
Then, with luck I can look for the second highest score in the next cell down from looking at the same range and so on and so on.....
In the attached sheet, I'm trying to get excel to return the top 5 values in an array, based on the preceding cell. This example is for a Football League, in which the QBs, RBs and WRs all have different salaries. The objective is to return the salaries of the top 5 paid people in each. Don't need to return the name, just the salary amount, so that an average can then be taken to figure future salaries.
View 5 Replies View RelatedIs it possible to highlight the highest value within a given range in that particular cell ? Example: I have a range of value from cells A1 to A10. The highest value is in cell A3. Can I make cell A3 highlighted in colour red?
View 5 Replies View RelatedI am looking for a formula that will add the 8 highest values in a range of up to 20 cells. The range may have fewer than 8 values in some cases in which case the sum of all those values will be returned.
Thus a row with these values will yield the following output (bold denotes highest 8 values):
8, 28, 45, 13, 17, 19, 21, 30, 47, 8, 15
47+45+30+28+21+19+17+15= 222
I have a simple list of alphanumeric asset numbers,
AKR DC 0001
AKR DC 0002
AKR DC 0004
And so on, I want to run a piece of code from a user form which will return the highest value in the range plus one on the numeric portion. I can find the largest value easily with a sort and last used cell value and I could drag out the numeric portion, increment and crowbar it back in but I'm sure there is a more elegant way.
All asset codes follow the same naming convention and all reside in column a of the active worksheet.
I have three columns in one Sheet (Sheet 1). The Columns are: "A" - Student Name; "B" - Start Date; "C" - No. of Courses; "D" - ID Number. The columns contains student data for 50 kids all in random order.
On a different Sheet (Sheet 2), I want to list the Student Names (Column "A" of Sheet 1) of the Top 5 students, according to their Start Dates (Column "B" of Sheet 1) latest to earliest. If two students start in the same date, I want to rank them according to the No. of Courses (Column "C" of Sheet 1) for the duplicates (large to small), and then by the Start Dates for the rest of the student. And if the Start Date and the No. of Courses for two students are the same, I want to rank them according to the ID Number (Column "D" of Sheet 1) for the duplicates (Small to large), then by the Start Dates and finally by the No. of Courses for the rest.
In addition, Sheet 1 has other information in corresponding cells stretching up to Column "J". When the Top 5 rankings will be made in Sheet 2, data points for the students shall automatically be pulled from Sheet 1 and inserted in corresponding columns within Sheet 2.
I understand its a very complicated problem. I challenge is exacerbated by the fact that I cannot use the sort function. And If the data is changed in Sheet 1, the Top 5 rankings will automatically update with the new information in Sheet 2. In addition, I am not to use VBA and have to complete the project using regular Excel functions.
I have a row of upto 20 numerical entries, (1,1,2,2,10,10,3,3,4,5,6,7,8,12,1,1,7,8,9,11) what formula do I use to go about getting excel to return the two highest numbers?
In this example I'd want excel to return the numbers, 12 and 11
How do i take the three highest numbers from E4:E5 & E13:E14 and add them together in E26
View 3 Replies View RelatedI am trying to add the highest 4 or
View 9 Replies View RelatedI'm trying to wrap up a spreadsheet and I'm trying to figure out how to have my formula use the highest of 2 numbers when making a calculation.
For example:
If:
Cell A1 = 5000
Cell B1 = 3828
I want the formula to use A1 in this case. However, if the total of B1 = 6000, I want the formula to use B1 instead of A1 in the calculation. (A1 = 5000 will always be a constant/base).
I need to know how I can create a formula which will sum a selection of the highest numbers in a range of data. So for example, if I have a row of 10 cells each containing dfferent numbers, and I want to take the 7 highest values from there and get the sum of these values, how do I go about this?
I've tried using the LARGE command but can only get an individual figure.
how can I get excel to show the highest and lowest figures in a range to display in another 2 cells. Can this be done without having to sort the data and remove all of the other rows except highest and lowest. I am using Excel 2003.
View 5 Replies View RelatedI am trying to work out how to select a range from a formula.
One formula works out when a specific number in a list of rows is reached, and returns the number of values it counted before it reached the number.
MATCH(TRUE,INDEX($A$2:$A$50>=B1,0),0)
With the figure returned from that can I then select the range from the first row, to the number of rows counted. And with that selection find the highest number within that range, specifying the highest number as the result?
I am trying to work out the 'Upper Percentile' of some donations we have received.
To do this I thought it would be rather simple, I just used
Code:
=LARGE(A1:A100,ROW(INDIRECT("1:9")))
This worked but obviously puts the values in 9 different cells, my work would like it shown as 1,2,3,4,5,6,7,8,9 in one cell.
a formula to rank a set of data ? The smallest data will be rank 1 and the highest will have the last rank. this range of data has some spaces in between, that needs to be ignored but should not be deleted. I have attached a spreadsheet stating the case.
View 4 Replies View RelatedI have a spreadsheet that is comprised of 3 columns:
Column A - a list of values
Column B - the rank of the value in the adjacent cell in column A out of all values in column A
Column C - the quartile rank (1, 2, 3 or 4) of the value in the adjacent cell in column A
I would like to create a formula that would return the highest value in column A that is ranked in the 2nd quartile.
Is there a formula I can use to find the Highest and Lowest value in a column?
View 9 Replies View RelatedI have 6 rows (A-F) with dollar vallues. I am trying to create a formula on row G that will give me the highest dollar value out of rows A-F.
Basically, I have a worksheet that shows test results for 6 separate tests. If an objective is met in a test then it is marked with an "X". If an objective is partially met then it is marked with a "/" and if it is not met then it is marked with a "." . I have now created a combined column where I want to collate whether an objective has been met at all across the 6 tests.
For e.g. if in the range H14:M14 there is an "X" then N14 should show "X". If there is no "X" but there is a "/" then N14 should show "/" and it there are neither values in the range then it will show "." .
I tried
[Code] ....
but it just came back with a formula error.
I WANT it to search all of column A for the highest number in the range (which will change daily to a new high number above the highest currently), and return the value in column 4 (column D) that corresponds to that value from column A. This is what I have, but it is not working, it is giving me the number (1) in Cell A800 (which is the lowest valued number in my range. The numbers range in value from 1 to 762 with none repeating (they are sequential). The formula I have is :
=+VLOOKUP(MAX(A76:A2000),A76:I2000,4)
I have a column that updates automatically by placing a value in in the next blank cell in the column. I am trying to write two formulas to identify the highest and lowest values in the preceding 10 cells of each update in the column but seem to be stuck in MIN/MAX/OFFSET hell.
View 8 Replies View RelatedListed below are the grades for a student. Some columns have blanks. I have to find the highest grade (A being the highest) in the range.
I'm going up with the opposite (F) with this formula:
=CHAR(MAX(INDEX(CODE(A22:M22&CHAR(1)),0)))
How do I get A?
I'm using Excel 2007
A
B
C
B
D
E
A
A
B
F
I've a spreadsheet (see attached) which shows sales of 10 different fruits. I've used a LARGE formula (Cells O15:O17) to display the three highest sales and an OFFSET formula (Cells B15:B17) to show what the are.
I only ever want to show the top three sales but I need to take into account instances where more than one fruit has the same sales figure (for example, if lemons and pears both had sales figures of 27, they'd have to be shown along with grapes and strawberries).
Is this possible???
I am building a football stat database which I am looking to put in an individual sheet which will search the other 20 sheets, collate the information and then display a top 5 ranking in each area. For example, this is our setup for the data:
Screenshot 2014-03-12 21.56.18.jpg
So what I want to to is have a separate sheet which will have a section for say over 1.5 goals %. The formula would go and retrieve all of the % results for each individual team. Then it would rank the 5 best teams and display the team name in the first cell and the % in the other cell.
