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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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
Finding The Address Of A Cell
I've got a spreadsheet where column A has dates and time and column C has over 8000 rows of numbers. I'm looking to pull the date and time from column A associated with a value in column C... how do i do a "lookup" in this case?
View Replies!
View Related
A Macro To Finding Maximum Value
i have a table same this in 5000 rows and 10 columns that are years from 1998 to 2007. ISO2 is two digit names of countries. ISO2 D1998 D1999 D2000 D2001 D2002 D2003 D2004 D2005 D2006 D2007 CL 135 147 180 192 48 629 494 137 135 359 CL 132 145 177 190 45 623 491 134 132 356 CL 129 141 174 186 42 617 488 131 129 353 EC 126 139 171 184 39 611 485 128 126 350 EC 123 135 168 180 36 605 482 125 123 347 EC 120 133 165 178 33 599 479 122 120 344 KH 117 129 162 174 30 593 476 119 117 341 KH 114 127 159 172 27 587 473 116 114 338 KH 111 123 156 168 24 581 470 113 111 335 PH 108 121 153 166 21 575 467 110 108 332 PH 105 117 150 162 18 569 464 107 105 329 PH 102 115 147 160 15 563 461 104 102 326 PH 99 111 144 156 12 557 458 101 99 323 VE 96 109 141 154 9 551 455 98 96 320 i want a macro to advance filter column A and make a unique list of it and then find the maximum value of each country in column A in 10 years. for example final table for country "CL" must be same this ISO2 D1998 D1999 D2000 D2001 D2002 D2003 D2004 D2005 D2006 D2007 CL 135 147 180 192 629 629 494 137 135 359
View Replies!
View Related
Finding Equivalent Of Maximum Value In Another Sheet
I have to sets of data, each in a sheet with the first column as identical for both sheets. Sheet2 contains two series of 6 rows, each for a specific first column (""B Code"). Now I want to find the values of "TCT-C" column in sheet1 (for each range of 6 rows that the "B Code" is match) which corresponds to the row number that "TCT" in sheet2 is maximum (again for 6 rows). The point is, I need to shift to another series of 6 rows in sheet2 once the "B Code" does not match.
View Replies!
View Related
Finding The Maximum Value Of All Those Meeting A Certain Criteria
Problem: Range A2:C10 contains the login and logout times of various ID's. Each ID could log in and out a number of times a day. How could we find the first time a specific ID logged in and the last time that same ID logged out? Solution: For each of the ID's in range A2:A10, enter two Array Formulas. To find the first login time (Column B) enter the following formula: {=1/MAX((A14=$A$2:$A$10)*($B$2:$B$10 To find the last logout time (Column C) enter the following formula: {=MAX(($A$2:$A$10=A14)*($C$2:$C$10))} ID______Login Time______Logout Time 1 ______02:40___________03:10 2 ______00:15___________03:20 1 ______06:20___________09:30 3 ______09:14___________11:05 4 ______11:00___________19:30 2 ______04:05___________06:55 3 ______12:08___________17:17 1 ______10:00___________16:20 2 ______08:12___________12:33 ID______First Login Time______Last Logout Time 1 ______2:40__________________16:20 2 ______0:15__________________12:33 3 ______9:14__________________17:17 4 ______11:00_________________19:30
View Replies!
View Related
Finding Maximum Number For A Range
selecting a range under a condition. I'd like to write a macro to find the maximum value for a certain ID. The number of ID rows (left column) is unknown so can't say range("A1:A5"). Then have the ID and max number shifted to another column. Maybe use 'rows.count' ? Eg. 4009 5 4009 9 4009 8 4009 4 4005 7 4005 3 4005 9 3008 10 3008 11
View Replies!
View Related
Finding Maximum Value While Excluding Some Values
I have a row of data with the cells I want to find a MAX for are separated from each other: a1=300 d1=500 g1=800 j1=10,000 I want to find the MAX value for a1,d1,g1,j1, but exclude any value over 1000. So instead of =MAX(a1,d1,g1,j1) returning 10,000, =???(???) will return the next highest max of 800.
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
Finding The Maximum Number Before A List Turns Negative
i have the volume of a reservoir in column A for lets say, 2 years, with monthly data (so 24 numbers in my list) I also have some variables that feed into this list such as rainfall (fixed), and population (i.e water use, which i can manually change) if i increase the population too much, the volume of the reservoir will at certain times of the year, fall into negative digits. what i'm looking for, is a cell which works out the maximum value (in whole numbers) for my population, without any of the numbers in the reservoir volume list falling below zero.
View Replies!
View Related
Finding The Maximum Occurrence Of Text Over Non-consecutive Cells
I’m trying to figure out how to show the highest occurrence of text in 10 cells which are not next to each other. Some of the cells will also be blank And the other thing is if there is a draw e.g. 5 2Z and 5 1Z I'd like it to show that, either with a word e.g. "DRAW" or anything really other than just putting whichever of the 2 "notes" in it feels like Attached is a spreadsheet (all other data deleted) that shows what I am wanting to do – I have typed the results I am after directly in to the cells in columns BE & BF – BE will need some kind of countif, but when I try that is says I have too many arguments! I have to leave the columns in between blank in this case as they have other info in them, which is really annoying as if those 10 cells were contiguous then the formula
View Replies!
View Related
Finding Duplicate IP Address
I have columns B3 C3 and D3 Each have the header below net_addr1net_addr2net_addr3 It looks like this net_addr1net_addr2net_addr3 3 0 0 I have 3456 rows that have IP addresses. I need to either highlight the duplicate IP address and or use column I3 to put a word "Duplicate"
View Replies!
View Related
Finding The Minimum Time And Maximum Time
NameTime InTime OutAlan08300930Alan10001030Alan12301630Tony11301230Alan09450950Tony10301115 I would like to find the minimum time in and maximum time out for each person. The data type of Time In and Time Out are general. I.E NameTime InTime OutAlan08301630Tony10301230 Therefore, I would like to know what function in excel will enable me to perform such task. Furthermore, can this function use with VBA?
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
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
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
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
Incorporate Adjacent Cell In Formula W/o Naming Cell Address?
I searched and I keep finding something that's not quite what I'm looking to do so I think I'm missing something really simple. Let's say that I want each cell in col b to be the sum of 5 plus whatever value is in the cell adjacent to it on the left (col). I know that you can just write a formula in each cell like... b1 will be =sum(a1+5) b2 will be =sum(a2+5) ... but is there a shortcut so that you don't have to write out the actual cell address for each one? Meaning - is there a predefined name or something that represents the cell to the left or right so you can just use that instead, allowing you to just copy paste the same formula all the way down the column? Something like... b1 will be =sum(left+5) b2 will be =sum(left+5) ... where "left" represents whatever value is in the cell to the left of that particular cell?
View Replies!
View Related
Detect Cell Address Of Cell Directly Under Mouse Pointer
I've a sheet that presents the profitability of sales locations in a geographic layout (similar to Tables in a restaurant), each sales location is a cell, with roughly 1,600 locations presented. Each location is colour coded based on performance / measure, in a basic thermographic way [e.g. dark blue for very poor, solid red for high performance] with users being able to change metrics and re-colour cells accordingly. The sheet is already information rich, but I'm wanting to detect the cell address directly under the pointer and populate a text box or other cell based on the value in the cell directly below the mouse pointer. My question is; Is there a simple way of detecting the mouse position on the XL Grid? nb. Want to avoid having to activate cell beneath pointer.
View Replies!
View Related
Access A Cell's Contents Via A Variable Containing Cell Address??
How can you access a cell's value via a vb variable that contains the cell's address. I have a vb variable named cellAddress (string) that contains "$A$1" and I want to assign the contents of what cellAddress points to another VB variable - how do i Do this? e.g. if cellAddress = "$a$1" and A1 contains "xyz", I would like to assigne "xyz" to a new vaiable by referencing just cellAddress??? Also - Can anyone reccomend a good Excel VB book? A book that maybe stresses the VB language rather then a cook-book approach.
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
Obtain Cell Reference/address Base On Cell Value
I have this certain value "Y" in all the sheets. In the summary sheet, I would like to get the cell address for this value in a given sheet. I know that you can get cell address using CELL worksheet function. However, for that you need to know the reference to the cell, which is what I am interested in. I tried Hlookup to get column #. But couldn't figure out how to convert the number to letter so that I can use MATCH to get row # and then finally, index or address function to what I am looking for. If someone knows how to convert column # to column letter OR knows a better/easier way to obtain cell reference/address based on cell value,
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
Cell (Address ... )
I have created the below formula to return an address. I need to use this address in the second formula ("cell address here"), If i am completely off base please point me in the right direction.. =CELL("Address",INDEX($BO$23:$BO$40,MATCH($BE$4,$BO$23:$BO$40,0))) =OFFSET("Cell Address here",10-$BI$1,0,1,1)
View Replies!
View Related
Address Of A Cell?
I have a variable Num that covers the range of C5, C6, and C7. I would like to figure out how to write the code that grabs just the column portion of say C5. Set Num = Range(Range("C5"), Range("C5").End(xlDown)) Something like, Range(Column(Num), 3).Value or something like that
View Replies!
View Related
Cell Address
regarding the cell value. What i am trying to do is I have alot of cells with datas and in cell(1,8) I have a data. I will search through these cells and if they match with data of cell(1,8), it will return me the cell (x,y) with x and y being the row and col numbers. Is there any command that I can continue with the below code to get the x and y? For Each cell In Range(Cells(3, 4), Cells(500, 26) If cell.value = cell(1, 8).value Then
View Replies!
View Related
Formula Reference Cell Address In Cell
I have 2 sheets. In the first sheet, I have 3 columns. The first column contains transaction codes. The 2nd column contains the destination code (like a mapping cell where the value of the 3rd column would go but in a different sheet/2nd sheet) and the 3rd column contains the values for it. Would it be possible to reference a destination in a cell itself? Or should I just code it individually? I wouldnt want to reference each data one by one because the total would be 480 reference cells. Is there an easier way?
View Replies!
View Related
Find A Cell Address
I have the following macro written: Sub GT()Dim cell As rangeDim BeforeNull As StringDim FirstNull As StringDim InitialData As StringDim B As VariantFor Each cell In range("A1:A8")Alpha: InitialData = cell FirstNull = InStr(1, InitialData, " ", vbTextCompare) If FirstNull = 1 Then InitialData = Right(InitialData, Len(InitialData) - 1) cell = InitialData GoTo Alpha End If If FirstNull "" And FirstNull "0" Then BeforeNull = Trim(Left(InitialData, FirstNull - 1)) If FirstNull = "" Or FirstNull = "0" Then BeforeNull = InitialDatacell = BeforeNullNextEnd Sub Now after the final Next, I need the macro to select the last cell changed. Is there a way to do this?
View Replies!
View Related
|
TRACKER
