Lookup Nth Largest/Smallest Value
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
ADVERTISEMENT
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
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
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
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 23, 2007
I have a workbook with 4 sheets with Carrier rates based upon weight breaks and cities. I.E. Carrier 1 services City 1 @ 1.30 per LB for 0 -100LBS and 1.55 for 101 - 199LBS. What I need is a forumla so that someone can input the city and weight on sheet 1 and it will search the 4 sheets and return the cheapest Carrier in the event that there is two carriers with the same rate it would return both. It would also need to return the rate for that city and weight.
For example
User inputs "City 1" "112 LBS" formula searches the 4 carrier sheets and returns values "Carrier 1" & "1.55" as this is the cheapest carrier and the rate they charge per LB for this city.
View 9 Replies
View Related
Nov 14, 2006
If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. But this is not correct as the value I am seeking falls with a range of Min/Max Values therefore the return should be the Next Largest Value
I have values I would like to lookup against a table Min Max values
Col A Min Value 700,000
Col B Max Value 799,999.99
The value being sought is 745,000
I would like the return to be 799,999.99
But currently returning 700,000
Current formula is
=VLOOKUP(G2,tblMaxValues,2,TRUE)
Where G2 is 745,000
tblMaxValues is a list of values for comparison
,2, is the column to be returned
View 3 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
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
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
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 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