# Return Cell Address Of Maximum Number?

Jan 20, 2009i'm looking to return the cell address of the max number in a given range?

View 3 Repliesi'm looking to return the cell address of the max number in a given range?

View 3 RepliesADVERTISEMENT

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.

What formula can i use to obtain the address of the cell which contains the highest number? My range is A:A

View 2 Replies View RelatedI'd like to have the cell address returned along with a value when I use the

MAX function.

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

I have a got a table ( 10x30) with random numerical values.

How can I return a column number with maximum value?

Values will be changed periodically so the maximum number could be in different column each time

Example:

A-B-C-D-E-F-G

1-2-3-4-5-6-7

2-6-8-18-1-1-8

2-5-6-9-12-1-7

The results will be 4.

How to return address of the column or cell I selected from Application.Inputbox, not just return the value?

[Code] ....

* 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.

I was hoping that my formula would give me the count number based on the Maximum time (latest time) and the Name field...My result is a 0 instead of 62 (the correct answer).

=SUM((Download!$H$2:$H$10=A4)*(Download!$D$2:$D$10=MAX(IF(Download!$H$2:$H$10=$A4,Download!$D$2:$D$1 0)))*Download!$I$2:$I$10)

Would a Index/Match/MAX function be more efficient?

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.

I have a range of cells (C29:BB29) containing zeros and integers, I want to lookup the smallest non-zero value from that range in another range (C11:BB11) and return the cell address from C11:BB11 to use in a further SUM formula.

Is that possible?

I'm trying to return the cell address of an lookup. The lookup result is correct, but when I try use the below Address function, it just returns the text value (i.e., the formula itself) instead of the cell address. Below is the an example of each: ...

View 4 Replies View RelatedI want to be able to display the location of a specific cell no matter if I move it around by inserting cells above it. I've used =ADDRESS(41,2,1,TRUE,B41). Cell B41 contains the word "Tommy". So the result of this formula returns this: Tommy!$B$41. But now if I insert a row above cell B41, "Tommy" moves to cell B42 but the formula still returns: Tommy!$B$41. How can I get the formula to display the new cell address of "Tommy"?

View 8 Replies View Relatedconstruct a formula that finds a value in a range , then returns the cell address of that value.

Say, i wanted to find the amount 12385 from another sheet , range C2:AA12 (contains only numbers , no duplicates). result should give me the address of that value.

I have tried the address & match function but gives me an N/A error.

when I use the range.find function to find a certain value in a column.. i want to return the address of the cell.. and save it in a range variable. how would I do that?

so this is what i have now.. but Rng does not return as a range.. it returns 69... when i use ctrl + G and type ?rng

set rng = .range(A:A).find(what:=69, After:=.Cells(1, 1), Searchorder:=xlByRows, searchdirection:=xlPrevious)

ok so say it picks up..... cell A69... how do i get it to save range A69

The format for the sheet is set in stone and can't change, and therefore my macro's operate on set columns / rows amd cells.

Because the sheet is huge, im trying to figure out a way to only run the macro's that need to run and more specfically on the changed location.

The Question

I need to create a routine that on the user changing a cell, it returns the cell position (row,column), then it will call a routine i have already which decides based on the cell location, which macro to run and where.

Is there a way to get the vlookup function to return the cell address, rather than the value, of the lookup?

View 9 Replies View RelatedI have a list of ID's on one sheet that are also located in a large matrix on another sheet.

This macro uses the Find_Range function to find each ID within the matrix and return the column header where the ID was first located.

There is also a line which return a list of ranges, indicating all the places where the ID was found.

What I want is to convert the list of ranges into a list of corresponding column headers (ie row 1 of all columns in the range)

' Number of id's in list

RowCnt = Application.WorksheetFunction. CountA(Columns("A"))

For I = 2 To RowCnt

LookFor = Sheets("ID List").Range("A" & I)

Set InRng = Sheets("Matrix").Cells

Set Found = Find_Range(LookFor, InRng, xlValues, xlWhole)

On Error Resume Next ' If value is not found

Sheets("RateID Count").Range("C" & I) = Sheets("Matrix") _

.Cells(1, Range(Found.Address).Column) ' Return column header

Sheets("ID List").Range("D" & I) = Found.Address(False, False) ' Return cell address or range of addresses

On Error Goto 0

Next I

Here is a sample of database,

A

1

Jan-12

2

Feb-12

3

Mar-12

4

Apr-12

5

May-12

6

Jun-12

7

Jul-12

8

Aug-12

9

Sep-12

10

Oct-12

11

Nov-12

12

Dec-12

If i lookup a value in the range A1:A12, say Sep-12, i need to get the cell address instead of the value of the row. but i know how to get cell address using CELL function. but i need to get cell address when i lookup the value.because lookup value will be dynamic.

Is there any formula to find text and return its cell address? This is like when we use Ctrl+F function to find a text, and Excel then highlight the cell contains the referred text. At the same time, the name-bar displays the address of the cell. Now, is there any formula that can perform such task like this?

View 9 Replies View RelatedHow would I return a cell reference (address) to a cell that contains the largest number in a list?

I tried using "Address(large....) where I get the correct column, but the returned row # is the actual value in the cell (the highest # in the list).

I eventually will want to delete the highest number to leave the cell blank.

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.

View 3 Replies View RelatedI am performing a lookup using the populare user defined function nlookup, which does not take a lookuparray argument to find lookup values. But I only want to return lookup values that fall into a certain array. To accomplish this, I figured out that I need a way to specify a condition of the type "if cell address of lookup value falls within range"

It should be possible if I find a way to return the cell address of the nlookup value, but as nlookup is not limited by a lookuparray argument, so I was not able to use the address/index/match that come up in similar questions.

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

I have something like the following:

If Worksheets("RawData"). Cells(1, nColumn).Value = Worksheets("RawData").Cells(12, 16).Value Then

..do code (I know the cell location of interest at this point)

End If

Then I End up needing To Do something Like the following

ActiveChart.SeriesCollection(1).XValues = _

Worksheets("RawData").Range("L1:N1")

“L1:N1” above is a sample. Given the code above it I have the cell location in question but it is in the format such as .Cells(12,16). That doesn’t do me much good when range wants something like “L1”. I don’t know how to convert that (12,16) to a L16 for example.

I am setting a variable to the value of an absolute cell reference. I want to set the value of another variable to just the row value of that absolute cell reference.

View 4 Replies View RelatedIf I added two numbers together in one cell - how can I use the result as a cell address? example. A3=23+5 now I want to use A3 (or 28) as a cell address resulting in D28

View 4 Replies View Relatedi 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.

If I have names in one column and amounts in the next column, how do I determine the maximum of the amounts and return the associated name from the other column?

View 2 Replies View RelatedFormula to return the max of certain data.

This is data for an electric car. T= Trip and C=charge.

Column F is the parking time between Trips (T).

For each "T" event in column E, I would like to return the MAX parking time available before the last "T" event for that day (date). I've highlight the last "T" events in red.

I have attached an example : forum2.xlsx

Column G is an example of the output that I would like to achieve.

Note: For the last "T" event of the day it can just return the actual parking time (shown in green).

Copyrights 2005-15 www.BigResource.com, All rights reserved