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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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
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
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
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
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
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
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
Finding The Cell Address Of Maximum Value
I have a range of overtime data. I want to list the maximum occurrence of first 10 overtime data ( Like 3200,2950.2300....etc) Thereafter from the results obtained I need to know the cell address of the employee corresponding to the results obtained, i.e XYZ, ABC..... A B Employee Name OT Amount 1. XYZ 3200.00 23. ABC 2950.00 35. WER 2300.00
View Replies!
View Related
Determine If Cell Maximum In Range
I have a spreadsheet where I need to check if one cell value is greater than 6 other cell values in the row, then have it enter 1 in another cell if so. For example: C1=5 C2=2 C3=3 C4=2 C5=7 C6=0 C7=4 The largest value is in cell C5, so I would like C6 to show that it is by entering a 1 in there.
View Replies!
View Related
Determine Cell Above Maximum Value Of Row
[TABLE] item01/11/200702/11/200703/11/2007 a1710 b2611 c359 [/TABLE] I have the above table. I would like to determine which is the item that has Maximum value on a particular date. example on 2/11/2007, the maximum value is 7, so the result should be item a. How can I achieve this by using a formula.
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
Offset Cell From Found Maximum In Range
I recently asked how to locate a max value within a variable range using a macro and got the following responses: Get Maximum Value From Graph / Chart, all of which worked great. Dim r As Range Set r = Range("D2", Range("D50")) Range("K1").Value = Application.WorksheetFunction.Max(r) Range("Max1") = Application.WorksheetFunction.Max(ActiveChart.SeriesCollection(1).Values) Start = "D24" Finish = "D163" Range(Start, Finish).Select myrange = Selection.Address Range("Max2") = Application.WorksheetFunction.Max(Selection) Now I would like for a different cell to return the time value located one column to the left of the max value found above. I've tried to adapt other offset formulas that I've found to my purposes and haven't been able to make it work. how to do that using the max value as it is found in any of the above three ways
View Replies!
View Related
Force New Active Cell When Maximum Number Of Characters Is Reached
We have a form that requires descriptive comments to be entered into several rows of merged cells. My goal is to have the form be able to automatically dropped down to the next row of merged cells when the current row of merged cells reaches a maximum number of characters. And finally, the last row of merged cells would not allow any more characters than the maximum assigned but not advance to another cell automatically. The rows I am working with specifically are: Merged Cells F23:R23; A24:R24; A25:R25; A26:R26;...A29:R29
View Replies!
View Related
Copy/Fill Down Formula To Maximum Row Of Last Cell In Columns
In a worksheet I retrieve data from a SQL query. Now I have to add 5 different calculations per row and each calculation will be stored in a new cell (so 5 columns will be created) I want to do this with a loop macro and calculate this till the last "filled" row. The number of rows differs every month. What loop code should I use and can somebody give me an example of the VBA code. Note that some formulas contains nested If-functions.
View Replies!
View Related
Macro - If Cell In Column Is Blank Then Input Text Into Same Row In Another Column
I am looking for a Macro that will search a column for blank cells, and when one is found will add text to the same row in another column. For example: The below is a spreadsheet. I am trying to find something that will search through column "C" in this case and add text (of my choice) to Column "A" if the cell is Blank. So Since cell C1 is Blank then Type "ERROR" is cell A1. A B C D E 1 X X X X 2 X X X X 3 X X X X X 4 X X X X 5 X X X X
View Replies!
View Related
Conditionally Format Each Group Of Rows And Each Column And Color The Cell In Each Column
I've a worksheet that is 3750 rows of cells from A to DT and contains only numbers with the exception of a header row. The rows are in groups, primarily 4 rows, although there are exceptions - these rows are separated by blank rows. Small example as follows. 0:001:002:003:004:005:006:007:008:009:000000-296567-6-1-400000286567-300-230000715557-16-11-40-18141755226-348405-362203081417192125-893581061800-2443-11124-289326-81318-1105027854331331211318-10216-161235526785691450451115951-17-1882505656683093102671056-191-33-2710000554740971-61-78-34224107-169-128-17090179-85220-55-63-1-162-140-242-235328266319106113-214-64-148205-4108120-142076-167-60-68-6418081102-800000000000-3099695807185-80356138-123194186210677727125-23233092-2942944748055564476-422051-224-34461486649597117-84209-22-251-94 I have been trying to conditionally format each group of rows and each column and color the cell in each column of four (or less) numbers in each group that represents the maximum number. It was easy enough to come up with the conditional formatting BUT it will take forever to do this manually. I've been told to use a macro but I know nothing of programming. Has anyone seen or heard of a macro that might already be written to perform this onerous task.
View Replies!
View Related
Multiply Every Nth Cell By Another Column Whith Merged Cells, THEN Find Column Total
Please see the attached sheet. I have columns B through a lot (B through O in my oversimplified example). In every 7th row in each of these columns there is either a 1 or a blank/zero. I need to multiply that 7th number by the Quantity in column A, to achieve a total (ie the sum of each result of 7th cell*quantity) for each column in the bottom row, labeled "Totals". In the actual version of my sheet, there are far too many rows to select everything manually. I've been fiddling with combinations of COUNTIF/COUNTA and OFFSET, but I haven't come up with a way to check for the 1 in every 7th row, THEN multiply that 1 by the quantity in column A, THEN add up the results for each column. As you can see, there are 1's elsewhere in the columns that are irrelevant to this particular calculation, so something like LOOKUP would also have to look in every 7th cell and couldn't just look at the column as a whole. If you can't provide an immediate solution, but can at least point me to a resource that would allow me to devise a way to isolate every 7th row (THAT part is the sticking point), I'll surely post the solution to my own thread with updated keywords if I need it.
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
Automatically Fill In Column When Certain Cell In Column Changed
I am trying to have some automation to my WS and what I want to do, is if a certain column (column 104) is changed, it will fill out that column with the data entered as long as the account is the same (column 5). Here is the code I tried, but realized very quickly, it puts me into a loop. This is being called by a change worksheet fuction If Target.Column = 104 Then If IsEmpty(Target) Then Else testacct = Cells(Target.Row, 5) temptest = testacct s_TempSwitch = Target.Value Testoffset = 0 Do While temptest = testacct temptest = Cells(Target.Row + Testoffset, 5) Cells(Target.Row + Testoffset, 104).Value = s_TempSwitch Testoffset = Testoffset + 1 Loop End If End If
View Replies!
View Related
Automatically Copy Column To Last Cell In Another Column Upon Open
I have this code that I have been using fine until today when it just stop working for me... It's a simple copy and paste using VBA Code. The code finds the last row in Column A and copies the cell to L1. Below you will find the code I'm using and the line in yellow is where I'm getting the error. Private Sub Workbook_Open() Range("A1").End(xlDown).Select Selection.Copy Range("L1").Select ActiveSheet.Paste Range("A1").Select Application.CutCopyMode = False UserForm.Show End Sub This codes runs as soon as the worksheet opens and it has worked for over a year. I'm using MS Excel 2007. Please let me know what you guys think I have tried pretty much everything I can think of but i dont really know what i'm doing when it comes to VBA.
View Replies!
View Related
#N/A Is Always The Maximum?
In range A2:AAZ2 I have numbers but until the numbers are entered into, some cells in A2:AAZ2 shows #N/A. I want to calculate the maximum number in the range A2:AAZ2. My formula =IF(MAX(A2:AAZ2)>0,MAX(A2:AAZ2),"") always returns #N/A, instead of the maximum number.
View Replies!
View Related
Copy Every 9th Cell Value From A Column Into A New Column
I am really desperate about this. I have a column of which I only want every 9th cell value listed seperately one below the other in a seperate column. I tried defining the first two cells of the new column (e.g. first cell input is =B1 and second one is =B10, third one would be =B19 and so on...) and using the usual pull down. In short: I want to copy values from A1,A10,A19,A28...(always +9) into B1 (from A1), B2 (from PS: I have access to the 2008 Mac OSX version as well as to the 2007 Windows version!!!
View Replies!
View Related
Formula To Add Column A If Column B = Cell C1?
a b c d 1 .2 yes yes .3 2 .5 no no .5 3 .1 yes maybe 0 So, as in the above example, I need to put a formula in D1 so that it will take the sum of column a if column b = c1. I hope this makes sense. And keep in mind I will be continually adding rows to this and will need it to cover the entire row, not just part of a row.
View Replies!
View Related
If Column A = 0 Then Adjacent Cell In Column B Divide By .3
Im wondering if this can be done in excel.. 2 columns if the value in column O=$0.00 then I need to divide the adjacent cell in column L by 0.3 or multiply by 30% this is the way I was trying to do this... =IF((O2:O6722)=0,DIVIDE(L2:L6722).3) I have tried it several different ways and cannot get it to work - is it my formula or can this even be done because I can't reference just one cell?
View Replies!
View Related
Postcode In A Cell In Column A Appears In Column B
I want to see if a value in column A appears in any of the cells in Column B and to give the answer in column C. I am trying to match postcodes, so I want to see if a postcode in a cell in column A appears in column B. I am aware the IF function will see if it appears in the cell in the ajoining row but I need to select the whole column dataset.
View Replies!
View Related
Conditional Maximum
In the attached excel sheet, i am trying to find conditional maximum of a set of numbers. In Cell C3, I have the formula =MAX(($S$4:$S$500=$W$4)*(M4:M500)) entered as array function and is working fine. In Cell J3, I have the similar formula MAX(($S$4:$S$500=$W$4)*(J4:J500)) entered as array function.. This is not working...always gives zero. The difference between Range (m4:m500) and (j4:j500) is that, m4:m500 are all positive numbers and j4:j500 are negative.
View Replies!
View Related
Maximum Textbox Value
I have a userform that captures 12 textboxes worth of numbers. I want to find which box has the max value(two or more could be equal, would want that info). I wanted to do this without putting the values back into cells to do this. Is there an easy way? Or would that be the optimal way to do this?
View Replies!
View Related
Find The Maximum Value
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....
View Replies!
View Related
IF And MAXIMUM Statement
I am trying to generate a column that shows which organic suites are needed for each site. I have attached a sample of my spreadsheet. It is difficult to explain what I am looking for, so I have just put some bullet points down. Each suite required for each site Only 1 of each suite to be counted for each site This will show a total number of each suite needed What I have so far seems like it is close, but it aways reads as 1 if something is in the row. I want it to read 1 if there is a 1 in either row for the same site and 0 if 1 is not present for either row for each site.
View Replies!
View Related
|
TRACKER
