# Find Smallest In A List

Mar 8, 2007
I am trying to find the smallest values in a list. that part is easy (use SMALL). But, I want the ref to the cell where that value is in, not the value itself. Additional problem occurs when more than one of these values are the same. Example: A1 = 1; A2 = 3 ; A3 = 2 ; A4 = 2 ; etc. If I want the smallest, I want "A1" as result, for the 2nd smallest I want "A3", for the 3rd smallest I want "A4". So you see that a MATCH formula will not work beceause some of the values are the same.

View 2 Replies
ADVERTISEMENT
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
Mar 30, 2007

Is there a forumula that will allow me to find the smallest number in A1:A10 that is also larger than the number in B1?

View 14 Replies
View Related
Feb 11, 2010

I'm using this =SMALL(A:A,1) in a cell but i need it to ignore the number 0.

Because where theres no data it shows 0, but i need it to pick the lowest from the places where that data actually shows. something like this

0

0

54.2

0

82.6

0

0

55

So i need t get that 54.2 out of there.

View 9 Replies
View Related
Mar 10, 2014

I'm looking to find the smallest number in a column that fits a certain criteria, and I'm not sure where to put the if function, or if I can use some other 'qualifiers'.

I was thinking something along the lines of If(E:E<>2, Small(E:E, ROW()-1),____) but I'm not sure what the "false" argument for the if function would be in the blanks. I don't want there to be one.

Where E:E is the range I'm looking in, and 'not equal to 2' is the criteria I'm looking to have filled.

So, "Find the smallest number in E:E that does not equal 2" would be the wording.

View 5 Replies
View Related
Mar 10, 2009

I've a large excel file contain a sheet of values. With a Macro I would like to

1) find the smallest absolute value in a certain column

2) find the row number where this value was found

3) Copy certain values from this row to another place.

View 4 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
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
Sep 2, 2008

I need a formula that will give me the smallest number based on multiple criteria.

For example, I want to find all:

1. Product A

2. 1 Liter

3. Smallest Price

In other words, out of all 1 Liter Product A's, I want to see the smallest price point.

I tried a sumproduct but I can't get it to work. Something like =sumproduct((A1:A100="Product A")*(B1:B100="1L")*(small(C1:C100,1)))

How can I get the small formula working within the sumproduct? Or is there another workaround?

View 9 Replies
View Related
Oct 2, 2008

I have been using the SMALL function to find 10 smallest values in a list, however, some of the values are the same, is there a way of only having the 10 smallest unique values within a list?

View 2 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
Feb 6, 2014

I am looking for a formula to search two columns.

1) The first column needs to be search to match a text string.

2) If the text string matchs, I need excel to evaluate the number in the second column and find the MIN value of all numbers associated with the text string.

So:

Joe 50

Jim 12

Joe 10

Rob 25

Jim 8

Rob 99

When searching for "Joe", the function would return 10. When searching for "Jim" the function would return 8. For "Rob", it would return "25".

View 4 Replies
View Related
Jul 31, 2008

I have a list of 200,000 e-mail addresses in a excel document in column A. What i am trying todo is find all the duplicate e-mail addresses within the list that appears more than four times.

I have tried this forumla in colunm B so it lists how many times the record appears: =countif($A$2:$A$244270,A2) which works fine and then a number appears next to each record. Then i pressed the sort filter to arrange all the records in number order so all the 5's are at the top of the worksheet but that does'nt always list them in the correct number order.

Basically all i want todo is find all the records that appear in my list more than five times then make a list of just one of those 5 records so i can remove them from my e-mail system.

View 9 Replies
View Related
Sep 28, 2013

Easy way to find the drop down list , if more than 500 list. at least the very first letter of the text.

View 1 Replies
View Related
Dec 21, 2006

How can i easy return the smallest number higer than 0 from a set of numbers?

View 11 Replies
View Related
Jan 25, 2013

I need a formula that will find the second smallest number in a row. The row can contain as many as 200 numbers with many of them being zeros. That makes using the "small" function impossible.

View 9 Replies
View Related
Feb 26, 2014

I have 2 columns of numerical values (A and B).. I want to make a formula that takes the Sum of A/the Sum of B, neglecting the smallest value of A and it's corresponding B value

For example...

19

20

20

20

15

20

Clearly, 15 is the smallest value, and I want to know how I can take the sum of A/sum of B neglecting the 15

Basically, I want the formula to do (19+20)/(20+20)

The actual scenario has a lot more than 3 values, and I don't want to go through all of them (they change frequently)

I know how to make the simple formula..=SUM(A:A)/SUM(B:B), but I do not know how to make excel recognize, and not include the smallest value..

View 3 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
Jul 24, 2009

The whole "story" is explained, in details, inside the attached WB.

View 3 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
Sep 17, 2009

I'm creating a spreadsheet and have had difficultly creating a function that returns the second smallest unique value.

For example if my data set is:

2, 2, 3, 3, 4, 5, 6

I'd want the function to return "3". I've tried playing around with the SMALL(array, k) function and trying to use a COUNTIF function for "k".

View 6 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 17, 2012

I want to count the smallest numbers in a range of 10 cells after omitting the 4 largest. I have been using the formula

=SUM(IF(ISNUMBER(LARGE(E5:N5,{1,2,3,4})),LARGE(E5:N5,{1,2,3,4})),D5),

This works perfectly in that in the desired cell it enters the 4 largest numbers and a specified cell. I then want to be able to total the remainder.

View 9 Replies
View Related
Mar 18, 2013

I'm after a formula to find the smallest unique value in a range

For example..

1

1

2

3

Small(Range,1) = 1

Small(Range,2) = 1

But i need the second row to show '2' as the result.

View 7 Replies
View Related
Nov 24, 2006

I'm struggling to get the syntax right for this formula. I've been trying variations on the following formula but can't get it right:

=AVERAGE(SMALL(IF(AP8:AZ80,AP8:AZ8),1)))

Columns AP:AZ:

1.8921.7621.6501.1741.2261.2991.3020.0000.0001.1461.333

Any column in this range could have a zero value.

What I need: The average of the 8 lowest non-zero numbers in range AP:AZ

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
Feb 16, 2008

Find smallest numbers in the range ignoring zeros.

I want it returned as value "1", all others - as value "0".

Example: range A1:J1 contains - 5,0,7,3,6,5,8,0,9,3

desirable result in the range A2:J2 - 0,0,0,1,0,0,0,0,0,1

View 9 Replies
View Related
Aug 21, 2008

In column A, I have a list of names.

In column B, I have a list of values.

I want column C to show me which name has the smallest value from column B.

In other words, if there are 5 names, I want ONE name to give me a value of 10 (for the smallest value), and the rest of the names I want to show zero.

I also have in column D another list of values.

In column E, I want to show first, second and third place amongst the list of values from column D. The rest of the names I want to show zero.

View 17 Replies
View Related
Jan 2, 2009

I am looking for a function like MATCH if the match type were set to -1. However my data is sorted in ascending order. I am mining data from a Pivot Table, and it has dates across the top. Of course the pivot table will have the data sorted in ascending order from left to right. I want to find the first date that is greater than today. With weekends and holidays I can't just use TODAY()+1. Is there a function that can do what I am asking? Also I do not want to change the pivot table itself.

View 9 Replies
View Related
Dec 29, 2009

Currently we are using the formula below to get an average of column E in a spreadsheet. I was wondering how to modify the formula to get the smallest number in that column rather than the average.

=IF(ISERROR(SUMIFS(E:E,A:A,K2,B:B,K3,C:C,K4,D:D,K5)),"Not Available",AVERAGEIFS(E:E,A:A,K2,B:B,K3,C:C,K4,D:D,K5))

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
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
Apr 3, 2013

a formula to rank a set of data ? The smallest data will be rank 1 and the highest will have the last rank. this range of data has some spaces in between, that needs to be ignored but should not be deleted. I have attached a spreadsheet stating the case.

View 4 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
Jul 30, 2014

way to remove those highlight in red by any function with the following criteria.

When the period (i.e. date 1 and 2) are the same,

-Keep the cheapest available (if price is 0, it means unavailable)

-Keep all cheapest even when their price are the same

date 1date 2supplierprice

01-Jul02-JulA US$16

01-Jul03-JulA 0

01-Jul03-JulB US$22

02-Jul03-JulA US$15

02-Jul03-JulB US$15

02-Jul03-JulC US$18

View 5 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
Apr 14, 2009

Is there any formula witch i can use to find the smallest prime factor of a number?

Example: In A1 i write 30 (30=2*3*5 (the prime factors))

in B1 i write a formula, and the result will be 2 (smallest prime factor)

What is the formula?

View 7 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