# Return Cell Address Of Largest & Smallest Value In Column

Jun 2, 2009
How 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.

View 3 Replies
ADVERTISEMENT
Jan 14, 2010

I am using the following array equation to return the value in column 1 for the smallest 5 numbers. It works for small 1 and 3 but i get a #NUM! for 2 , 4 , and 5. The smallest 5 numbers are:

0

3.196408441

4.491972091

4.491972091

4.491972091

Equation:

=INDIRECT(ADDRESS(SMALL(IF((NumRange)=SMALL(NumRange,1),ROW(NumRange),""),1),1))

View 12 Replies
View Related
Aug 18, 2014

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

[Code] ....

View 8 Replies
View Related
Jan 12, 2009

I am using excel 2007 and I need help with the following if anyone would be kind enough. I have a dynamic array that consists of stream of 1's and 0's. I want to be able to find the largest sequence of 1's and the smallest sequence of 1's. So at a point in time the array mite be 111110001101111000 and I want to be able to dertermine the longest contiguous sequence of 1's. But as I say the array is dynamic and so I need to do that after each input.

View 8 Replies
View Related
Aug 17, 2007

Is there a way to reverse a lookup function?

I have a s/s attached, basically B is like an output of figures after some calculations. And I am trying to find from largest to smallest values and then having column E reflect the Letter beside that number...

tried using match but messed up.

View 3 Replies
View Related
Jan 25, 2014

I want to find the largest and smallest value for a number... E.g.

68734 the largest value is 87643 and the smallest value is 34678

39823 the largest value is 98332 and the smallest value is 23389

43089 the largest value is 98430 and the smallest value is 3489

View 4 Replies
View Related
May 10, 2013

There must be something I'm doing wrong, because I have a list of numbers and when I select Data --> Sort --> Column G, I am not given the option of sorting "From smallest to largest". Only from "A to Z" or viceversa.

View 3 Replies
View Related
Feb 20, 2014

I'm trying to create a formula that will allow me to filter the dates from largest to smallest while having a text in the formula. Right now I'm using:

=K2-M2&" Days"

But the dates are all over the place,

View 3 Replies
View Related
Jul 1, 2008

I have a work document with multiple rows of information. In each row I need to locate the maximum and minimum values. (The rows aren't long - there are about 10 values in each). I then would like to turn the font of the lowest value in each row red - and the font of the highest value in each row blue. I would also like to be able to skip blank or unimportant rows.

View 2 Replies
View Related
Dec 5, 2013

I am looking for a formula that will output the highest and lowest value in a range. Column A contains a range of numbers, some of which repeat and in column B i'd like to extract ONE instance of the highest and lowest value in Column A, preferable the last instance.

See example below and in column B the desired output.

Column A

160

90

120

90

120

160

Column B

blank

blank

blank

90

blank

160

View 5 Replies
View Related
Jun 21, 2013

I am doing a macro that will access a specific sheet of my workbook, a specific table, and will sort the table according to the field "Nominal", from largest to smallest. I have tried with this but it doesn't work:

Sheets("F.C.SSA-View 2").Select

Application.CutCopyMode = False

ActiveWorkbook.Worksheets("F.C.SSA-View 2").ListObjects("F.C.SSA_View_2").Sort.

SortFields.Clear

ActiveWorkbook.Worksheets("F.C.SSA-View 2").ListObjects("F.C.SSA_View_2").Sort. _

[Code] ....

View 1 Replies
View Related
Aug 13, 2008

I am having some data running into 60 (C:BJ) columns and 200 rows. It is the record of dates query raised and answered with refernce to query number in the A coulmn. I have zero value as entry for the unused cells in this range due to some constraints. I need to find the smallest and the largest non-zero date in this range for analysis purpose. I plan to use the difference between these 2 dates to calculate turn around time for a query.

Problem is to find the smallest and the largest non zero date within range C6:BJ6 which would be used as input in the Networkdays formula.

View 9 Replies
View Related
Nov 4, 2013

See post below. I want to have the column listed as per to auto sort to the largest to smallest value. This value will change throughout time.

I have data in columns a-n and rows 3-14. I want column "l" or the 12th column to auto sort from largest to smallest value.

View 1 Replies
View Related
May 15, 2008

I am trying to manually format a few graphs in my sheet. I, however, leave the major units of the axes to be automatically calculated. The code that I use is given below:

ActiveChart.Axes(xlValue).Select

With ActiveChart.Axes(xlValue)

.MinimumScale = Range("Min").Value

.MaximumScale = Range("Max").Value

End With

Now what this does is that it sets the maximum of the scale exactly equal to maximum of the range being plotted and hence a few parts of my graph overlap with the border of the plot area. Is there any way in which I can round the max and min scale of y-axis to the next highest and lowest major unit respectively or may be add and subtract a value proportional to the major unit of the y-axis. I cannot add a constant in the code given above since I that does not suite my requirements.

View 2 Replies
View Related
Jan 15, 2007

I'm using the LOOKUP function to find the largest number and then return the name from a different column, it looks like this:

=LOOKUP(LARGE(round1!$F$2:$F$65,1),round1!$A$2:$A$65)

but all I get is hashN/A?

View 14 Replies
View Related
Mar 21, 2012

very simple and short code.

Code:

Dim selection As Range

Dim minimum As Integer

Sub Sequence()

Set selection = Range(Cells(4, 3), Cells(6, 5))

minimum = Application.WorksheetFunction.Min(selection)

' and now I need an address of that smallest number for this:

' IF (column number + row number) > value of that smallest number

' do

End Sub

It's not exactly the program I need, but it's simple.

View 9 Replies
View Related
Jan 11, 2008

I am trying to get the addresses of different cells containing numbers. This addresses should be ranked by the size of the value from largest to smallest.

Currently I am using this formula

=CELL("address";OFFSET($A$2;MATCH(LARGE($A$2:$A$10;1);$A$2:$A$10;0)-1;0))

and just increasing the variable of the LARGE function for every rank.

This works fine up to the point some numbers are the same. If I have for example two cells containing a 80 and 80 is the largest value, rank 1 and 2 result in the same address (of the first cell containing the 80). But I want the formula to result in specific addresses for every number.

I thought about using MAX instead of LARGE, but I don't get a working solution for the second (third, fourth, ...) largest value.

View 9 Replies
View Related
May 12, 2012

I have a table showing interest levels in training courses from a group of schools, eg:

English Maths Science

School1 3 4 2

School2 7 1 0

School3 3 2 5

I want to identify the column heading for the first, second and third most popular courses. ie for School1 the most popular course is Maths, second most popular is English and so on.

I have tried using the OFFSET function, which worked if I provided the cell location of the required value. I then looked at the ADDRESS function to provide the cell location: eg For School2 find the 2nd most popular course:

=ADDRESS(ROW(A3),COLUMN(data?)+MATCH(LARGE(B3:B5,2),B3:B5,0)-1)

But I have got stuck with what I should enter for COLUMN(data?) as I do not know in which column the second largest value is.

I am sure Excel has the required functionality.

View 12 Replies
View Related
Oct 4, 2006

I am wanting to allow the user to select a cell in a worksheet and return the cell address and information/value from the cell. For example, if "Dog" is in cell A1 and "Cat" is cell A2 and the user initiates the Macro I would like the macro to to pop up a message box and say something like "Select a cell in Column A". When the user clicks on cell A1, I would like the message box to pop up and say, you selected "Dog" in cell A1.

View 4 Replies
View Related
Aug 17, 2009

I want to find the largest cell in a column so I can use “auto fit selection” without cutting off type.

View 9 Replies
View Related
Feb 10, 2007

I have a large set of numerical data, with each column having a text heading in the first row.

I want to use a function similar to HLOOKUP that can return the numerical value in a certain row of a column that has a specified heading....AND if there is more than one numerical value that meets the criteria, I want it to return the lowest numerical value.

My numerical data cannot be sorted into ascending order.

View 9 Replies
View Related
Aug 18, 2008

I want to ask if there is a way to find the next greatest maximum without a ton of auxiliary coding....Is there a function which does that in Excel? For example from the following list it would give 45:

1

34

45

26

78

29

7

View 4 Replies
View Related
Aug 15, 2006

I know function Vlookup finds the largest value less than or equal to the given value. However, If I want to finds the smallest value larger than or equal to the given value, How can I do it? assuming I do not want to use VBA.

View 5 Replies
View Related
Oct 9, 2006

I found this spreadsheet on ozgrid, but can not locate the original post to move my question to -- mods please move if you can.

As for the question:

The spreadsheet attached searches an array, and shows the top 3 scoring names. How do I modify it to show 3 lowest scoring names without changing the rank field?

View 9 Replies
View Related
May 1, 2009

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?

View 9 Replies
View Related
Jan 24, 2008

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 Related
Feb 29, 2008

I 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 Related
Mar 13, 2008

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 2 Replies
View Related
Jul 1, 2014

I am very competent using the index match formula and have incorporated this into many spreadsheets to auto-fill for avoiding input errors. The objective I am attempting now is to retrieve from a list of 2800 entries the smallest return on an index match formula.

From the data below if my Index Match formula uses C1 to index and match my return is always a7 (-12.7) the first in the list, where my desire is a return of a19 (-14.11). To complex this problem I often have duplicate amounts that need to be listed when they fall into my parameters. I have figured that the Small function allows me to assign first smallest, second smallest, and so forth.

I prefer not to use vlookup for simplicity and error avoidance issues.

a b c

1 amount Reason for Movement 2250

2 -183.252150

3 -366.612150

4 -61.1 2154

5 -91.652150

6 -4.34 2450

7 -12.7 2250

8 -2.98 2250

9 -2.98 2250

10 -69.372452

11 -0.6 2451

12 -4.49 2250

13 -4.49 2250

14 -4.14 2250

15 -4.7 2250

16 -4.55 2250

17 -4.85 2250

18 -1.41 2250

19 -14.112250

20 -8.47 2250

View 13 Replies
View Related
Mar 16, 2014

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

View 3 Replies
View Related