Find The Maximum Value In One Column And Retreive The Name Of Cell In Corresponding Row
Feb 9, 2009
I spent couple of hours trying to figure this out, but without success. Please check the attached example. I need a formula that will find the max value (number of views in English anguage) in the column "I" and display the corresponding "Headline" value in the column "B". In the example it is "Headline 5".
I can not change position of the columns, i.e. "B" must remain "B" and "I" must remain "I".
I experimented with Index and vlookup but to no success. I have outlook 2003.
I'm developing a spreadsheet for a nonprofit that provides feedback on their major fundraiser/auction. I'm trying to create a formula in a cell that will look at the bidder #'s in column B and add the value of the their purchased items found in column C and return the number of the bidder that has the largest total value of purchases.
This can be done with a pivot table and give a table of all bidders, their total amount paid for items, and the number of items purchased.
Look in Column E and find the MAX value. Once you find the MAX value, (let's say E27) display the date that's in C27. I bet this is really easy but I've been screwing around with it for over a half hour and can't get the correct result.
I have two columns of data. The first column is the 'type' and the second column is the 'value'. I need to find the maximum 'value' for each individual 'type'
The 'types' are not necessarily next to each other and the data cannot be sorted to do so.
Example: type value A 15 B 6 A 21 C 7 B 13
I need to be able to say the MAX for 'A' is 21, the MAX for 'B' is 13 and the MAX for 'C' is 7.
In column A I have a set of Ids that are not unique. In column B I have a set of latlong values for those Ids in column A. In column D I have the unique list of Ids from column A(Removed duplicates from column A & pasted them in column D). In column E, I need the latlong value which has highest frequency for each Id. I tried countif,Frequency and tried mixing them up with various other formulas but it is out of my reach.
I have a problem looking up the cell at which a maximum value in a curve is located. I have attached a workbook with an example of my problem (this is scaled down to limit the size of the attachment – my usual string of data is 5000 rows long). Column A is the time and all subsequent columns will contain force values (as shown in columns A and B) which create a similar sharp curve.
Ideally I need to be able to lookup the time point (column A) at which the maximum force value occurs for each curve or if this is not possible the row number in which the maximum force value occurs. I would like to be able to do this using a formula rather than a macro if that is possible?
I have tried using a number of excels lookup functions and read through the examples on the site but have had no success.
I wanted to know if there was a way to find the Maximum value given a number of conditions are satisfied.
Similar to COUNTIFS and AVERAGEIFS, where you state the data range to use, the condition's range, the condition, then the next condition's range, the next condition, etc....
Here the maximum value is 212, Is there any way to find its corresponding date. Date in Row(A1:A5), Values in Row(B1:B5). Tried my best to get the date; but no way.
I am having trouble with large data sets of rents by market: trying to find the peak rent in a time-series, and then look for the next trough rent in time. I tried using the Newsletter 3 techniques which feels like the right direction, but I am not getting the nesting right or some of the parameters in the functions.
So, within each time-series I need to find the MAX value (peak rent), wherever that is in the array. Then I need to look to the right of that exact MAX value and find the exact MIN value (trough rent), whereever that is in the array (again, to the right of the MAX value).
I have been trying without success to use Vlookup to lookup a value in one column, say the number 15. 15 in this column will fill cells in several rows as it refers to several different values in the second column. The second column only has one instance of each value, for example 42. Then with this information I have been trying to get Excel to retrieve a third value and return it to the cell from which I am entering
the formula......An example... 15............. 42 ................ yellow 15............. 42.5 ............... blue 15............. 43 ............... red 16............. 42................... purple 16............. 42.5................. green 16............. 43.................... pink
Is this asking too much of VBA? I don`t think so but cannot figure out how to properly enter a formula that will do this. I have tried Vlookup several different ways with no success. If I could get it to return the cell reference instead of the cell value on the first lookup excersize I could then make the second vlookup start at this cell reference to get the third number but I can`t even figure out how to do that.
The content of Cell A1 looks like this attccggttaattcccccaaaattt (only a,t,g,c -nucleotides). I want to know the max times C occurs in this cell and the position from the start. like that a, t, g.
here the answer is 5 times and distance is 13 from start.
I am looking for a way of finding the maximum value in column H for each row in a pre- filtered range using VBA.
For example, the result of of filtermacro1 may return several records derived from a database of several hundred records. In the example below, the name of the person is in column C, followed by their H column value.
Audrey Perkins, .5 Marc Bloomberg, 2.0 Matt Phillips, .5 Paul Pachson, 2.0
However, the H column value may not be the maximum value for the rows above, as there are other records for Audrey, Marc, Matt & Paul in the database.
If the names are matched to the max value in column H for each record it should read
Audrey Perkins, 1.0 Marc Bloomberg, 3.5 Matt Phillips, .5 Paul Pachson, 6.5
How would I query only the names in a pre-filtered range and obtain the highest value in column H for each ? These values then need to be copied to H7 in a sheet called Detention Register.
I have numbers in range B1 to B100. How do I search for the first negative number (not the smallest negative number) within the range? Then, retrieve the Date indicated along A1 to A100?
i have my data layed out as per below, is there a dynamic formula which can be used for each bank to retreive the the count against each criteria, so if there are more rec breaks for any of the banks the formula will adjust accordingly ....
I have inherited some legacy templates (Standard, Leave and Exception) which cannot be changed. I need to summarise them (Total) selecting the earliest start and the latest finish. (Sample attached). The templates are 90 columns wide and about fifty lines deep so named ranges isn't practicle (I think). I'm running 2003.
I have rows of 10 keywords. I want the macros to compare the first row against all the rows except itself, then compare the second row against all the rows except itself, the third row against all the rows except itself, basically looking for duplicates...It will then record in a column for each respective row, the max # of duplicates found with another row..
For instance lets say I was looking at the 15th row, and it looked for duplicates elsewhere. It only found duplicates in the 4th row and the 5th row. In the fourth row, there were 5 keywords common, and in the 5th row there were 7 keywords common. Therefore the max # of common words is 7 for the 15th row, and that is what is recorded.
In addition the number of rows will probably vary it could range from 1 to 500. I included an excel spreadsheet that has visually speaking how this could work, maybe using COUNTIF. Basically it counts the # of common keywords for one row with all the other rows, pastes the count #s to the right, then finds the max of those count #s, copies the max into another column, then clears the count #s, and looks up the next row, repeats the same process. It is easier probably if you look at the attached spreadsheet to get an idea of what i am talking about..
How do I find the minimum and maximum numbers in a table if I do not know the size of the table but can assume the user will choose the first cell (upper left cell) of the table and run the macro?
I have a column of values the values can be "T", 0.00 ->any number to the nearest hundredth. I thought I had this problems solved a while back but after having time to do some quality control I found that anytime there is a value of "T" the macro does not work. I have had some great help with this in the past to get as far as I have but I still cannot seems to get it 100%. I have attached a sample file. The result that I would like to see is in the cell BH145 is "16,17,23" without the quotes of course.
When you open the worksheet you will see that the values of concern are precipitation values. These values are rounded to nearest hundredth. When there is a trace of precip however it is recorded as "T" therein lies the problem that I cannot seem to solve.
