Return Corresponding Cell For Maximum In Another Column
* I hav two columns B and N having some data. From column N i need to find the Maximum valued cell.
* And now i need the content of a cell in column B in the same row , which matches with maximum value.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Return Heading Of Column With Maximum Value
In attached example, I have 3 columns of fractions with a final row of sumtotals (taken from elsewhere). I test to see which column has the maximum value in each row and allocate the result with the column's heading. Sometimes, two or more columns have the same max value. In this scenario, I wish to select the column heading that has the largest sumtotal.
View Replies!
View Related
Return Cell Address With Maximum Value
I have two columns of numbers in column A & B. In column A I am trying to locate the maximum value and get the cell address of the number next to it in column B. I am currently using this formula to do this ..... =ADDRESS(MATCH(MAX(A1:A4),A1:A4),2,4) It works ok, but if the maximum number is the first value in the column it is returning the last cell address in column B. I am not sure what I am doing wrong.
View Replies!
View Related
Return Maximum Of Active Cell & Arbitrary Value To Cell
I am looking for VBA that will add the value of the current active cell on the sheet to the value in cell F12. The maximum value of F12 cannot exceed 1000. So if the value in F12 = 950 and 100 is the value in the active cell the maximum value in F12 should show 1000, not 1050. It should do this on the click of a button.
View Replies!
View Related
Cell Of Maximum Value In Another Column
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.
View Replies!
View Related
Find The Maximum Value In One Column And Retreive The Name Of Cell In Corresponding Row
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.
View Replies!
View Related
Return MAXIMUM Based On Criteria
i have a spreadsheet like the following A B C Country Revenue Month 1 UK 10 Jan 2 France 20 Jan 3 US 30 Jan 4 UK 25 Feb 5 US 35 Feb 6 France 5 Jan and so on... So where country = UK, France or US I want to retrieve the MAX revenue from all months and which month it was in. Eg UK max revenue was in Feb of 25. I am not sure how to apply the max formula with criteria. Is there any way to do this?
View Replies!
View Related
MAXIMUM Function Limit: Return A Value?
What I'm trying to do is get the MAX function to return a value, but that value needs to come before the values reach zero. It's a cubic equation that basically starts, at the beginning of the data range, under 10 then hits zero and ends up near 30 at the end of the data range. The end data range max doesn't matter to me if the data range hits zero along the way. Is there any way to make the MAX function stop at a certain value, or is there another function that could accomplish what I'm trying to do?
View Replies!
View Related
Return Maximum Date Or Blank
I am currently using an array to return the MAX date value. This formula will enter 1/0/00 if the referenced cells are blank. I'd like the furmula to LEAVE THE CELL BLANK if the referenced cells are blank. {=MAX(IF(Bid_Circuits=$A45,Bid_Trim_Completed,""))}
View Replies!
View Related
Return Data Corresponding To Minimum & Maximum
Please see attached file which is data for horse races (this is a small example of the data i will be working with). I need to find the largest values from the MIN and MAX column (shaded Grey) range for each race. I want to be able to add a function that will allow me to quickly identify which horse has the largest number in both the MIN & MAX columns example on spreadsheet is highlighted RED. It would be handy if it would inform me if the criteria has been met by highlighting it or by placing some text value in an adjacent cell on my spreadsheet i have used the example "Y". It would also be beneficial if that race be deleted in its entirity if there are no horses which meet the criteria above or similary if all other selections within that race be deleted if ther is a horse which meets the criteria. If there is a selection within that race i will need to keep the row that includes the heading which is coloured blue on the attachment. I will be working with 20,000 or more selections at a time so it is important that they can be identified quickly for ease of use. I have tried the functions i know such as MAX and LARGE but this requires me to do alot of work when you consider the ammount of data i'm required to work with.
View Replies!
View Related
Lookup Maximum Value & Return Corresponding Row
i have a matrix index| a| b| c| 0001 |1| 2| 2.22| ( probably it will be much clear to see the attachment) I need to lookup for the largest number in B2:D2 (in this case 2.22) and return the corresponding header ("C"). I though it would be simple with hlookup() and max() but i can't make it work.
View Replies!
View Related
Find Value In Column & Return Cell On Same Row- Different Column
I have an excel sheet I am working on and in columns F1:F2000 I have an IF statment, I need to be able to add more "IF"s to it but I will exceed the 1024 char limit. Is there a way I can put this formula into VB as a function called DocumentType() and then in excel F1:F2000 =DocumentType()? Is it as simple as: Function DocumentType() If(...........) End Function
View Replies!
View Related
VBA Macro To Return Maximum Value In Text Form..
I am a novice to VBA in excel, i have a table of more then 5000 rows. I would like to find a maximum value from each row and return the column title as "GL_Weld" or "Bend" or "Header" into another worksheet cell. i.e First Row max value = 0.011510 should return "Bend" into another worksheet GL_WeldBendHeader 0.0001700.0115100.000500 0.0000000.0007800.000000 0.0002400.0110700.000480 0.0000000.0006600.000000 0.0000000.0037700.000000 0.0000000.0000100.000000 0.0002800.0108700.000480 0.0000000.0007100.000000 0.0002000.0112900.000490 0.0000000.0008400.000000 000.00002 00.000310.00001 000.00004 00.00030.00002 000.00001 00.000270.00003 000.00002 00.000260.00004
View Replies!
View Related
Lookup Maximum & Minimum. Return Corresponding Row
I have searched your forums and thought I had found a sufficient answer but could not get the vba to work. So any help is greatly appreciated. I am trying to determine a max value from a list then put that value in a cell. Next I want to determine how many times and on what day that max value occured. From there take the value and concatenate them adding a "," between them I have attached an example. I would like the values placed in cells F1 and H1 (the other is a min value and when it occurred)
View Replies!
View Related
Return Values In Range Based Upon Relationship To Maximum Value
I am looking to select two values from a range based upon their relationship to the maximum value. In this specific case I am trying to calculate the Quality factor based upon the frequency response of a transducer. So I need to identify the Maximum impedance and its corresponding frequency. I also need to identify the 3db points either side of the maximum impedance. The 3db points are found at 2/3rds of the maximum value of the impedance (Impedance Mag in attached spreadsheet). Calculating the first 3db point isn't overly difficult as I just calculate the value for the 3db point and use the MATCH function to find the closest Impedance value and then work out the corresponding frequency from that. I am stumped as to how to calculate the second 3db point (above the Max impedance value). I have attached a spreadsheet with frequency information in column A, the other columns are used to derive the values found in columns M (Impedance Mag) and N.
View Replies!
View Related
Return Only Unique Text From Another Sheet Along With Correlating Minimum And Maximum
I have, on one sheet, a list of incumbent employees that includes the following columns: Job Title [text] and Salary [number]. I am trying to automatically populate a second sheet with columns designating: A list of each unique job title (no duplicates), the maximum salary corresponding to each unique job title, and the minimum salary corresponding to each unique job title. Lastly, I'm an attorney dealing with confidential client data, so I'll have to go through a lot of trouble to post an example... but I'll do it if necessary.
View Replies!
View Related
Find Cell Value In Column & Return Text In Adjacent Cell
I have a drop down list in a merged cell B12-F12 and B13-F13 and B14-F14 . . . B30-F30. I need the adjacent merged cell to populate a reason (text) based on the text answer in the drop down list or the entered text in the first merged cell. For example in the cell B12-F12 the user picks from the list or types in "Amiodarone." I want the adjacent merged cell G12-J12to automatically fill with "Heart Rhythm." I also want to be able to set up multiple if - them statements like if Amiodarone is entered then fill adjacent cell with Heart Rhythm and if Toprol XL then fill adjacent cell with Heart / Blood pressure and if simvastatin then fill adjacent cell with Cholesterol, etc. I have about 30 different options for cell 1 that I want to have auto fill in cell 2 based on the contents of cell 1. I've attached my file. I want the user to be able to choose from the list or type the drug name in.
View Replies!
View Related
Return Last Used Cell Value In Column
Does Excel have an equation capable of taking a column range (say A1:A200) and able to pull the last number in that range? So, say for example, I have data populated from A1:A97 and every time I add a new number to the column (this would be A98, A99, etc.), the formula, in a reference cell at the top, will pull that latest number that I've entered? This way, instead of using a LAST equation and having to change it every time I enter a new figure, it will automatically generate for me in a another cell, say at the top of the page?
View Replies!
View Related
Return Value Of Last Used Cell In Column
How do you get a cell to display the last occupied value of that column in that cell? Example Col A has values 1,2,3,4,5... How can I get the last number (no matter how many more cells of that column are entered) to appear in the last row of that same column?
View Replies!
View Related
Return Column Number Of Cell
I have a Range variable that points to a particular cell. If I wanted the address, I would do, cellName.Address. This returns, for example, "$F$3". If I wanted to have the column "F" as a number (column F would be the 6th column, so 6), how could I do that without storing some kind of array of letters and number equivilants. It would also have to work past the 26th column when the column addresses switch to, like, "AA", "AB" etc.
View Replies!
View Related
Find Cell Value In Column & Return Corresponding Value
I am drawing a complete blank of this one. I know it can be done but I can't seem to remember how I did this in the past. I have a sheet that has cells A1 at MB, A2 as ML A3 as MD Then in cells B1 I have MB,TL then in B2 I have ML and then B3 is Blank this time. Then in cells C1 I have Jim, C2 has Jack, and C3 has Fred. What I want to do is have a formula that is in cell D1 that looks at the value in A1 and finds that value in Column B and if it is found return the value in the same row in Column C. In the example it would show Jim since MB is found in cell B1.
View Replies!
View Related
Take Value To Left Of Maximum Value In A Column
Trying to grab the value to the left of the max value in a range of cells. Wasn't sure if I needed to use the offset function, I tried the vlookup function but the value i'm searching for is in the 2nd column, not the first. I can't download files at work, so if you could examine the example spreadsheet i uploaded, and then type the formula I should use in the post.
View Replies!
View Related
Return Cell Reference From Multi-Column Lookup
i just want to use vlookup and hlookup to give me the row and column headings for 30 to 40 sesor at a time.... but i keep getting #n/a whenever the functions meet a table of more than one column! (reduced workbook attached) it should surely be simple to get this data - but i've struggled to no avail. I thought that having the four separate worksheets was the problem - but i haven't had any look even when i dump the data into a single worksheet! i basically just want excel to return the cell reference of a sensor number which exists in a table. sometimes a sensor can appear more than once, but its not very common and i could happily work around that by doing the manual search (ctrl f, find all). any advice would be very much appreciated, i'm struggling and the number of sensors i need to test will increase in the coming months.. please help! should i even be using vlookup and hlookup? ive tried all the other excel functions, but they don't seem to be useful?
View Replies!
View Related
Maximum Value In A Column In A Pivot Table
I am new to working with Pivot tables, and am working on a data set of survey results. We'd like to build a heat chart into the pivot tables for each column of data. To do this, I need to determine what the maximum and minimum values in each column are, and base the cell coloring on the difference between min and max values by quartile. Ideally, this would be able to update when the column headers change (from a list of departments, to countries for instance).
View Replies!
View Related
Formula To Return The Value In A Column Based On Cell Entry In Row
Trying to write a formula that looks for the first number in the same row, then returns the value in that column in a different row. In the example included, cell A-7 should contain the formula. Within row 7, determine the column where the first number is listed (column D in this case), then return the value listed in row 3 of that column ('C' in this case). I've tried Lookup, Index, and Match functions, but can't seem to get the right combination.
View Replies!
View Related
Find Date In Column & Return Adjacent Cell Value
I have a spreadsheet with a column A of dates April 08, May 08, June 08 etc. Adjacent to each of these dates is a value in column B. I want to select the appropriate value relevent to the current date and use it elsewhere. Therefore if it happens to be Oct 08 when I open the spreadsheet I want the value in column B, adjacent to Oct 08 to be represented.
View Replies!
View Related
Finding The Maximum Value In A Column Based On A Condition
This should be simple to do but I can't figure it out. I have a database that lists operating room numbers in one column and the length of the surgeries performed in those rooms in another column. I need a formula that will give me the longest OR time for a given room. For example the room numbers are in column A and the OR times are in Column B. I've tried something like
View Replies!
View Related
VBA Code To Go Through Column Cells & Return Value Based On Cell Value
I have been stuck for about a week on trying to create a Case Select macro. I am a novice in using VBA, so I am not sure what I am doing right OR wrong. What I need the macro to do is go through all of the text in a column and if the text is one of the 50 states, I need it to return one specific value, and if it is anything other than one of the 50 states, it needs to return a different value in a column 5 over from the column with the states in it. I have changed the code MANY times and so far I haven't been able to get anything to work. Here's the gist of what I have been trying: Sub usstates() Dim R As Range, s As String Set R = Sheets(1).Range("J1:J50000") For i = 5 To R.Rows.Count X = UCase(R.Cells(10, 1)) If X = "AL" Or X = "AZ" Or X = "AK" Or X = "AR" Or X = "CA" Then R.Cells(i, 2) = "SC" End If Next i End Sub.................
View Replies!
View Related
Find Data In Column Range & Return Cell Same Row But X Columns To Right
i have is 3 sheets in the same excel document. Sheet 1 is the mater sheet, which is a compilation of sheets 2 and 3, however the sheet layouts are different. The sheets consist of a list of names and details. Sheet 1 has all the names in the list, however sheet 2 and 3 only have partial lists that are in a different order from the original list. The details listed next to the names in sheets 2 and 3 are different and hence a straight forward row copy and paste will not work. What i need is to write a vba script that can take the name from the master list, search sheets 2 and 3 for the name and lookup the variables placed in next to the name, then update the master sheet accordingly, then continue to the next name on the master sheet and do the update again, and so on until all is updated. I have already thought about using lookup functions in excel however there is 1000 names on the list and around 60 details so it would be messy.
View Replies!
View Related
Find First Blank Cell In Column & Return Adjacent Date Less Than Or Equal To Today
how to make the data look like a table with three columns. Other than the date, it is space delimited. I have a tracking spreadsheet where Column A is populated with dates for the year. Column C contains daily values. I don't always start entering daily values on the first day of the year, e.g., this year the first value in Column C corresponds to March 9. All values in Column C are contiguous - there are no blank cells until the value in Column A is greater than today's date code. I would like to use a formula (rather than VBA) to look down Column C and find the first non-blank entry where the value in Column A is less than or equal to today(). In this case, the formula should return the value for March 9, 2008. CREATE TABLES LIKE BELOW?Column A Column B Column C March 1, 2008Saturday March 2, 2008Sunday March 3, 2008Monday March 4, 2008Tuesday March 5, 2008Wednesday ...................
View Replies!
View Related
Maximum Cell Value
In a cell, I would like to limit the maximum upper limit. For instance, in a cell with (=H23*0.06), that value ranges from perhaps 1.48 up to 5.93; however, I would like for the upper limit to stop at 3.33. Would that be conditional formatting? How can I stop the upper limit at 3.33?
View Replies!
View Related
Maximum Characters Per Cell
I have a worksheet that multiple users will be entering in data, this information is then being used to pull into a form located on a sepearte worksheet within the workbook. One of the cells in the input sheet has the potential to have more characters than excel will allow. It is my understanding the maximum number of characters per cell is 1,024. What is the best way to setup my spreadsheet in the event the data contains more than 1,024 characters. The informatino going into the cell are legal descriptions of property so I want to be able to keep the description together in the final document in the other worksheet. Is there a way the user can input the entire decription in one cell, regardless of the character size, then have a formula to take the first 1,024 characters and put them in one cell, followed by another formula to put the next 1,024 characters in the next cell? Then in my main document I would use the concatenate formual to combine these two cells into one.
View Replies!
View Related
Match Text In Column Of Another Workbook & Return Nth Column Same Row
I have two excel files. File #1 is a varying length and has 4 columns with ID #s in Column A. File #2 is a two column file that contains a list of ID #s in column A AND modified ID#s in column B. I need to match the ID #s in column A from file# 1 to the list in column A of file#2 then replace ALL instances of the matching ID #s in column A of file#1, with the values found in column B of file #2. I've tried using the substitute function but I could only replace one found ID at a time.
View Replies!
View Related
|
TRACKER
