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 RepliesI 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 RelatedIs 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.
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
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 RelatedI'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,
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
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] ....
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.
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.
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.
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.
how can I set up conditional formatting to highlight the smallest value, by row?
View 2 Replies View RelatedI need the largest value in a column to automatically highlight and the old high value to return to normal.
View 12 Replies View RelatedI have this excel spreadsheet and I need to calculate the biggest gap between the numbers below. (The gap cannot exceed 9 rows)
Example:
8/31/07 - 47.32
9/7/07 - 52.41
9/14/07 - 50.63
9/21/07 - 48.72
9/28/07 - 54
10/5/07 - 51
10/12/07 - 48
10/19/07 - 55
10/26/07 - 58
11/2/07 - 85
11/9/07 - 116
11/16/07 - 127
11/23/07 - 200
11/30/07 - 169
12/7/07 - 156
12/14/07 - 143
12/21/07 - 152
12/28/07 - 160
Biggest gap: 152
I cannot manually do every single row (this excel is gigantic). Isn’t there some excel formula to do this for me automatically?
I tried: =MAX(B1:B18) – MIN(B1:B18). Here I get the biggest gap between B1 (47.32) and B13 (200), but this gap is bigger then 1 month = 9 rows, which it is not allowed to exceed.
I was able to calculate the gap by taking the difference between the MAX(B1:B9) and MIN(B1:B9) and then repeating this one row down at a time ( e.g. B2-B10, B3-B11, B4-B12, etc)
This of course takes up a lot of space but does the job. If a shorter way is possible then please let me know. If not then I only need one thing: I need the chosen rows with the largest gap between its max and min to be automatically highlighted.
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 RelatedI have an excel sheet with multiple columns. In one column I have X's and in another I have a limit. I need to look down the stuff column, find every X, match each X to its corresponding limit and then compare all the limits and return only the smallest limit found. I.e if I was looking down the stuff column I would find 4,6,8,8,3 and thus I would return 3 as my value. I am very new to working with excel formulas,
limit
something
stuff
4
x
[Code]....
I'm using this formula to lookup data based on the lowest average.
[Code] .....
The problem is that the minimum average is a two way tie, and I need to return both data sets. It just happens to have the same average.
I've tried using SMALL(array,1) and SMALL(array,2) in place of MIN above, but it gives me the same data set. I've also tried resizing using offset.
See attached : sample.xls‎
I need a formula that would return the sum of the largest 3 numerical values out of a range of values, i.e. ignoring the lowest values in the range.
View 5 Replies View RelatedI have a row with 30 cells and each cell has a different value. i want to count the highest 15 cells of those 30, so the sum of the total only counts the largest 15 digits.
View 2 Replies View Relatedis there a simple way to sum the ten largest (or smallest) values? I just can't think of any formula to do the job, and a filter isn't an appropriate permanent solution.
How would I find the four rows with the largest value in a defined column and then export them to a separate worksheet.
View 2 Replies View RelatedI have lists of numbers in a columns and would like to elliminate high outlier values in each column. Each list is different so there's no way to just set a max value and say that any value above it is an outlier. What I'd like to do is to average the top 5 or so max values for example, and then say that any vale above this average is an outlier. So a formula such as =average (max1,max2,max3,max4,max5) would work if it's possible to derive max1, max2 and so on. Or maybe there's a different way of accomplishing this?
View 3 Replies View RelatedI am looking to create a formula to add together the two highest values of a list of 10 numbers.
I know you can use something like large(a1,a10,1)+large(a1,a10,2) if numbers are in an array but my problem is numbers arent
they are in random positions along a row....
I have 3 columns. In 1st there is a name of the product.In 2nd there are different types of product (6 in total) and in the 3rd column a figure attributed to the correspondend type of product. I need to create a summary for every type. For this I want to show the 5 largest figures(based on the name of product) for every type.
Like this:
AAA GK 40
BBB CD 50
CCC FD 55
DDD GK 60
EEE FD 70
.....
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.
I've got four tables with negative and positive values and want to determine the largest magnitude of a subset of four values within those four tables. In other words if the largest magnitude is negative I want the negative value of the four subsets or vise-versa.
I've tried the following but got an error stating it was too long:
= IF(ABS(VLOOKUP(table1)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table1), IF(ABS(VLOOKUP(table2)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table2), IF(ABS(VLOOKUP(table3)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table3), VLOOKUP(table4))))
The repeating line needs attention in my mind:
MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4)))
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))
I have a large list of students (Col a,b) who do several sports (up to 10) each, which are arranged as sport name (Col c) followed by score (col d), repeated on pairs of columns for the other 9 sports.
I need a macro to find the 4 highest scoring sports for each student in turn and to highlight the score and sport cells. I have tried to find permutations using the LARGE function but I cannot see how I can do this.
There should be a formula to extract the largest values and its corresponding values. For example, if column
Products Costs
A1 Pen 200
A2 Pencil 125
A3 Radio 670
A4 Apple 1500
A5 Xbox 222
A6 TV 100
The desired outcome
Products Cost
Apple 1500
Radio 670
Xbox 222
Pen 200
Pencil 125
TV 100
The second goal is extracting the top 3 largest values using a formula.
Sample file is attached for your convenience. Desired outcome is in GREEN.