# Output One Instance Of Smallest AND Largest Value In A Range

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
ADVERTISEMENT
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
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
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
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
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
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
Jun 6, 2014

I currently am working with a worksheet that has over 250,000 data points. It is a spreadsheet for a company in which different work order are organized. A given work order can have several parts (ranging anywhere from one component to 40ish) and thus the height of the cells in which each specific work order is placed differs from work order to work order. For each component, the spreadsheet tells you how many can currently be made based on the materials available. What I would like to do, for each specific work order, is go through the data and simply tell whether or not the work order can be completed (the quantity of each specific component needed is also given in the spreadsheet) and then report out exactly how many components can be made.

Ultimately, this means that a "limiting factor" analysis must be done. For instance, if the work order is asking to create plastic bottles that consist of the bottle, the cap, and the label and it asks for 3 bottles but only 2 caps are able to be made, the limiting factor would be the caps and thus only two bottles could be made and the work order could not be fully completed.

The spreadsheet is set up based on another worker here, I am just trying to come up with a way to analyze it. Cells A1:Axxxxx are the work order numbers and the size of these cells differ because they are merged to fit the number of components they have. The componenets available are then ranged C1:Cxxxxx etc and the quantity needed are also listed in a similar fashion. What sort of structure could I use.

View 7 Replies
View Related
Mar 17, 2009

D3 = SMALL(H6:H45, COUNTIF(H6:H45,0) +1)

to determine smallest value in range excluding 0 is it possible to link that smallest value to another value like a name?

Eg. H6 = 5 K6 = Ben

H7 = 4 K7 = John

H8 = 9 K8 = Ted

Thefore D3 = John

View 12 Replies
View Related
Jul 3, 2014

I would like to be able to find the largest visible area of continuous rows in a filtered table. I know one possible way would be to loop through visible cells using the "xlCellTypeVisible" property and count cells in each visible area. However, the data is consisted of tens and sometimes hundreds of thousands of rows so I was wondering if there is a faster, more efficient way to do this.

View 3 Replies
View Related
Mar 7, 2014

I need a formula in Column E to find the results for Column E or subtract A2 from C3, C3 from D3, A4 from C3, B5 from D5 and A6 from D6 .

table.tableizer-table {

border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif

font-size: 12px;

}

.tableizer-table td {

padding: 4px;

margin: 3px;

border: 1px solid #ccc;

[Code]....

View 5 Replies
View Related
Mar 26, 2009

I am trying to write code that will locate either a 0 or the smallest negative number in a range, eg,

example 1,

8, 6, 2, 0, -0.5, -2

the result would be 0

example 2,

8, 6, 2, -0.5, -2

the result would be -0.5

View 6 Replies
View Related
Mar 8, 2012

Lets pretend that I have a boat load of automatically generated values from a different program.

There's a specific equillibrium point to a certain measurement, but this measurement involves a lot of fluctuation. To get a near accurate result, I must find the maximum value in an entire graph on the Y Axis value(Column B). Ofcourse, this is easily done using the =MAX value

Exactly 450 seconds (X Axis value(Column A)) positively and negatively from this point, something like this happens.

Problems:

A.) I must find the highest points 450 seconds negatively ( X ) to the max of the entire graph.

B.) I must find the lowest point closest to the two points we uncovered, both on the positive ( X -->) side.

C.) The fluctuation of every spike can last to about ±30 measured points in the graph. (±20 seconds) Thus, the maximum point surrounding the +/- 450 seconds (±693 x values) needs to be accurately predicted as being around 450 seconds, not 10 seconds more or less.

D.) The measurement is known to something glitch out and suddenly get a spike somewhere along the road, which will then quickly fall back down, which is a false maximum level. How can I prevent excel from taking this as the maximal point?

Remember, this needs to be fully automated into a macro later on, ergo, I cannot manually add the formulas to all the (650 different) sheets.

I have 25 days before the project gets cancelled if this doesn't make it.

View 4 Replies
View Related
Jul 10, 2014

I have a list of text cells in column A, the list is a dynamic one that changes depending on the property selected in a cell of another sheet.

Example data... Column A is Block Name, Column B is Room Number.

---------A----------------B-------

1--Main Building-------001

2--Block A--------------001

3--Block A--------------002

4--Main Building-------002

5--Block B--------------001

6--Block A--------------003

7--Main Building-------002a

8--BSS House----------BSS

9--Main Building-------003

What I need is for a formula that lists one example of each instance included in Column A.

As per the example data above, the expected results when dragging down from C1 through to C9 would be:

---------A----------------B--------------C--------

1--Main Building-------001-------Main Building

2--Block A--------------001-------Block A

3--Block A--------------002-------Block B

4--Main Building-------002-------BSS House

5--Block B--------------001-------

6--Block A--------------003-------

7--Main Building-------002a-------

8--BSS House----------BSS-------

9--Main Building-------003-------

I know I could use a pivot table to do this but as I need this to be dynamic, and also to be used with Excel on an iPad Air, I can't as it doesn't support pivot tables or VB to automatically refresh it.

View 11 Replies
View Related
Aug 24, 2009

I have a worksheet with a column that lists the city where an item is located. If the city is say Austin I need to find the last cell in the range that has Austin in it. I can find lots of examples where you can find all instances within the range and to find the last cell in the range that has any data in but nothing that tells me how to find the last cell in the range that has a specific 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
Sep 2, 2009

I just used the excellent formula =row(index(range,match(1,index,--(range)>0),0),0))) to give me the first instance of a number, but i am wondering if there is a formula to give me the last instance of the number as well? Maybe? This would save me much time if possible.

View 11 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
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
Jun 22, 2014

I tried a few searches for what I need but I think my wording might be off or there isn't anything on it yet. So the formula I'm looking for is: I have a single colum of say 700 rows with random values in each. I would like to average rows 1 to 15 then 2 to 16 then 3 to 17.... all the way through to 686 to 700. Now out of all that I need only the highest 15 period average value returned. Seemed simple until I tried to do it. Whats that saying, the idea is 1% of the invention..

View 4 Replies
View Related
Jul 8, 2009

How to automate the process of finding the highest number in a dynamic range meeting a criteria?

New data added/updated every day (new come, old gone).

The attached file has a properly displayed data.

View 11 Replies
View Related
Aug 26, 2009

I'm looking for a formula to enter within a particular cell which will return the largest number that appears within a range of cells, for example the range H:133 through L:136. If it's any easier, only one number will appear within the range, though it could appear in any cell within that range.

View 3 Replies
View Related
May 19, 2009

I have a named range which refers to: =MATCH(LARGE($G$5:$G$68,ROW(G5)-4),G$5:G$68,0)+4. It is supposed to find the largest figure in the adjacent column, list its row in cell H5, then the second largest for cell H6 and so on until the end of the data. The data in column G is dynamic and some cells will contain "0" with certain sections of data thus why I need a formula and cannot just use sort each time.

My problem is that when I use the formula as a named range I get "#N/A" and when I evaluate the formula I will get the answer but in array brackets (e.g.{17}) before the final step turns it to "#N/A". The formula will work fine if inputted directly into the cell but as the same formula will be used over three columns and numerous rows I would much prefer to use a named range.

View 8 Replies
View Related
Jun 19, 2009

I need to create a macro that will examine a range of cells, for example D12:T12, find the cell with the largest number value in that range then set the font for that cell to bold (or red, or change the cell background color).

View 2 Replies
View Related
Oct 16, 2013

I have a set of data like below, I am trying to get a formula to look at a range i.e. May column and then select the highest number in that range but rather than returning the number returning the team name, is that possible? Not sure where to start...

Jan

Feb

Mar

Apr

May

Jun

Team 1

25087

23029

32991

21319

25271

25136.8

[Code] .........

View 4 Replies
View Related