# Formula To Find The Highest And Lowest Value

Dec 5, 2006Is there a formula I can use to find the Highest and Lowest value in a column?

Is there a formula I can use to find the Highest and Lowest value in a column?

I have two sheets:

Sheet one has dates, my prices on each of those days, and the fields h1 h2 h3 h4 (h1 is the closest higher number, h2 is the next closet higher number, etc.) and l1 l2 l3 l4 (l1 is the closest lower number, etc. etc.)

Sheet two has certain dates and prices of a competitor's prices.

What I'm trying to do is two things (see attached):

1) find the four closest higher and four closest lower prices (if available)

2) use only the prices that are on or before the date in question.

I've tried combinations like LARGE and OFFSET and so far it is not getting me anywhere.

Can this be done???

I am looking for a formula that will find the lowest 5 number group in column A with a total value from column B under $100 in the below table.

1 $28.75

2 $28.00

3 $27.75

4 $24.75

5 $25.50

6 $25.25

7 $25.25

8 $16.50

9 $24.75

10 $26.50

11 $24.50

12 $27.00

13 $26.50

14 $23.50

15 $23.00

16 $19.25

17 $20.75

18 $25.00

19 $20.50

20 $20.50

21 $17.75

22 $22.50

23 $20.50

24 $19.75

25 $20.00

26 $10.75

27 $11.25

28 $5.25

29 $13.00

30 $9.00

I'd like to record the highest / lowest value in a single cell without it being written over i.e record the highest value and if there is another value lower it wont overwrite it.

I've tried using the =max or =min but whenever a newer value appears in the cell it just follows that without keeping the higher value?

Could any of you Excel bods please help me find the correct formula to enter in order to calculate the following reasonably simple sum:

3 cells with numbers, say, 1, 3 & 7.

I simply need to get my worksheet to look at all three cells and then calculate the result of adding the biggest and smallest number together. i.e. 8 in the example given.

I have 7 values I want to sort from the lowest to highest value. These values are measured in feet so I include the sign for feet. " ' "

The values are:

9'

6'

5'

15'

12'

11'

10'

They will not sort correctly for me. Above is how they sort in descending order. Which is not correct.

I have A bunch of numbers going up to 4.0 from 0.0 how can i get it so that it takes the number and the name beside it and buts it in a colume from 1 to ..... complete

ex)

David 1.5

Jon 3.5

Sally 4.0

Susan 3.24

Fred 2.99

I am trying to do is rank the value in column G from the highest positive value to the lowest and then the highest or largest negative to the lowest or smallest negative in that order. The way it is arranged/sorted in the attachment is exactly how it should be for this example. However, the ranking formula does not work? What it needs to do is show the value in cell H13 as the 6th ranking and not the 13th?

View 3 Replies View RelatedI have a cell to receive real time data during a period of time(the period depends on other cells). My problem is I want only the greatest value of the real time data. How can I manage this?

View 6 Replies View RelatedI am looking for a way to find a highest (and/or) lowest value in a list of numbers. The list keeps getting longer and after a certain point I will need the 2 highest (or lowest), then the 3 highest (or lowest). I know MIN and MAX will find the high and low, but how do I find the 2nd highest, etc?

View 3 Replies View RelatedI have multiple rows, 1400 to be exact, that has a set of 6 numbers. I want to order them from lowest to highest in another column. Here is what I mean:

4 6 1 3 2 5 => 1 2 3 4 5 6

Is there an easy way to do this? I assume a macro would be easy, but to add a twist, can it be done if you don't use a macro?

what I need for excel to automatically remove the highest and lowest TOTAL POINTS and create an average "Speed Rating" of the remaining 3 scores.

Is it possible to get Excel to do this?

I got some data as below :

...........column B..... column C

row26....... a............. 5

row27....... b............. 6

row28....... c............. 4

row29....... d............. 7

.

.

.

.

.

row38....... f............. 7

column B is static and column C will increase automatically from other formulas.

What I want is to sort column B and column C from the score in column C - highest to lowest automatically.

I am trying to configure a formula using RANK to do the following:

- Sort scores from highest to lowest (range from 1 to 900)

There will be 64 entries total (64 man bracket) and i would like to rank them from highest score (900 is highest score) to lowest score (1 is lowest score).

This is for a bowling bracket. The names of the bowlers will be in column A. The scores will be in column B. Their RANK will be in column C.

I have a data listed from A1 to G1. What I want to do is

Check out all the numbers written in those cells, take the highest number among and write to the following cell (lets say A3)

also

Check out all the numbers written in those cells, take the lowest number among and write to the following cell (lets say A4)

I need a formula that will pull specified information from sheet2 (without having to sort) into sheet 1 by looking up the specified name. See attachd file.

View 3 Replies View RelatedI have a data that needs to be ranked from highest to lowest. I used the Rank function to do this.After getting the rank, i need to consolidate the data by highest to lowest rank, while simultaneously getting the values of the column on the same row.

I used the index and the match function. And i was able to do that, but the problem is there are items that have the same rank. how do i consolidate the data from highest to lowest even if with same ranks?

how can I get excel to show the highest and lowest figures in a range to display in another 2 cells. Can this be done without having to sort the data and remove all of the other rows except highest and lowest. I am using Excel 2003.

View 5 Replies View RelatedI am trying to generate a list of the 5 highest and 5 lowest numbers from a list of scores that range from 1 to 10. I have found the highest and lowest absolute values (numbers over 7.5, and less than 5, out of 10), but I would also like to generate the 5 highest and lowest relative numbers, ie. if there are no scores over 7.5, then the 5 next highest numbers. I have used if/then formulas for the absolute highest and lowest numbers, and a rank/countif formula to rank them. I have no idea how to generate a list of the "relative strengths and weaknesses".

View 14 Replies View RelatedI need to do conditional formatting based on the highest percentage (value) in each row.

[1st highest value (green coloring), 2nd highest (Orange color), 3rd highest (blue coloring), if it has the letters "NT" then (Red color)]

But to break the tie, if the value of the 1st column is equal to the value of the 2nd column, 1st will be considered as the highest value. If the value of the 2nd column is equal to the value of the 3rd column will be considered 2nd largest value.

For a competition with a lot of participants I'd like to make subtotals of the score. The participant with the highest weight gets 1 point, the second highest gets 2 points, ... and so on. How do I make a formula in the points column with this exercise?

Nummer

Name

Weight

Points

Weight

[Code].....

I am looking for formulas for cells B21 and B22 that will return the value from column A corresponding to the occurence of the highest/lowest value of Index compared to cell B:19, that is, the most recent Index data.

This seems to be an excellent candidate for LOOKUP as the data in Column A are unique and sorted.

Then, we have cells B:25 and B:28. On what "Week Ending" did the Min/Max value occurred?

Bonus Question, if Excel encounters more than 1 value that satisfies the formula, what happens?

************************************************************************>Microsoft Excel - Book1.xls___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB24C24B27C27=

ABCD1Week*EndingIndex*AIndex*B*208/11/06561.2385.9*308/18/06561.1382.2*408/25/06556.5375.9*509/01/06566.3389.7*609/08/06584.2410.2*709/15/06595.8397.9*809/22/06566.5463.8*909/29/06633.9375.9*1010/06/06599.1383.3*1110/13/06585.8384.7*1210/20/06588.6382.4*1310/27/06570.8375.6*1411/03/06620.9402.2*1511/10/06620.9402.2*1611/17/06623.6401.4*1711/24/06599.0406.7*1812/01/06647.6426.6*1912/08/06612.2393.4*20****21Lowest*Since***22Highest*Since***23****24All*Time*High647.6463.8*25Date:***26****27All*Time*Low556.5375.6*28Date:***29****Sheet1*

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I have a sheet of about 15000 rows made up of about 1300 groups( events) and 40 columns, a miniature of which is attached.

In column1 I have the event identifier,column 2 contains a score or rating for each contestant in each event.,in column 3 there is a code for each competitor,either orange or pink.There will be at least 1 orange and 1 pink in each event.

column 4 is the one I want to create by formula,the lowest pink in each event

divided by the highest orange. I have titled this column the spread. I have filled column 4 manually to illustrate what I mean.

I'm holding a Reverse Auction where people pay a dollar to place a bid, BUT the twist is they are giving me a number that they how will be the lowest number but it has to be the only occurrence of that number (greater than 0).

During the party people can guess as much as they want to pay. I think i'll use a spreadsheet with their names in column A and go out in the row with however many cells for how many numbers they guess (so there would be blank cells in the overall range of the whole list if one guy buys 10 numbers and another only 1, for example).

So, i need a cell at the bottom that tells me the lowest number that wasn't guessed more than one time.

I've found how to FIND duplicates and the lowest number but i don't know how to write it so that it discards the duplicates.

I can't find a way to sort my data. It looks similar to below. And I need to sort them based on the numbers from lowest to highest.

Tom Simmon (23.2)

Jason Paige (19.1)

Jeff Johns (14.1)

I have data in a worksheet coming from an external device that is updated via DDE. The values in the cell change every few seconds. I would like to record the highest and lowest values that these cells contain.

I want the peak values to be stored in other cells.

I need the close% column to auto sort from highest to lowest so that I can see at a glance who the top sales person is. I have conditional formatting for the top three but I would rather them auto sort by close%, can anybody help me with this. I have attached the file,

View 9 Replies View RelatedI have a column that updates automatically by placing a value in in the next blank cell in the column. I am trying to write two formulas to identify the highest and lowest values in the preceding 10 cells of each update in the column but seem to be stuck in MIN/MAX/OFFSET hell.

View 8 Replies View RelatedI am currently trying to display a number from a column of data, where the number is the smallest, then the second smallest (third, fourth and fifth where applicable). When using =small, I am able to display the second smallest number, but when the list contains duplicates, the second smallest figure often matches the smallest. I am having the same problem with =large. I have tried to combat this by using an IF statement, but am only able to place so many arguments into the formula before excel is unable to perform the formula. This is also proving quite lengthy :o(

View 4 Replies View RelatedTrying to arrange numerical values in order from lowest to highest in a column i have a code to do that but i need it to be able to but to a range in the column so it only sorts the values in rows 3 - 60 not all of the rows in the column

Sub Testsort()

Range("A3").CurrentRegion.Select

Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal

End Sub

I have imported and filtered a .csv. to specified sheet names. I have rows that have been sorted by a specific column's cell contents. i.e.

A B C C E F G H I

xxx xxxx xxx xxx xxx 1 xxxx xxx xxx

xxx xxxx xxx xxx xxx 1 xxxx xxx xxx

xxx xxxx xxx xxx xxx 2 xxxx xxx xxx

xxx xxxx xxx xxx xxx 2 xxxx xxx xxx

xxx xxxx xxx xxx xxx 2 xxxx xxx xxx

I need to be able to select all the rows or ranges that contain a common value 1's and then loop back and select the next group 2's of rows until the row or column contains "".

need to understand the easiest way of highlighting the highest and lowest number in a range of cells several times in the same column.

As I said the cell range will be in the same column. So highlight the highest number green and lowest number red in cells a3 to a9 and then repeat the same process again in cells a11 to a17 and so on.the cell references are an example.

So I have several separate sets of numbers in the same column.

I have a list in column A of percentages (to 4 decimals 0.0013) i need to conditional format the list so that it shows the lowest percentage in 'green' and the highest in 'red'.

View 5 Replies View RelatedI'd like to have a list printed of all the "qualifying people" found in a range, and then sort their corresponding price values from highest to lowest in Columns A and B. EXAMPLE:

RANGE: D3:D20 - Numerical

RANGE: E3:E20 - Text (names)

RANGE: F3:F20 - $$$

I'd like to search column D for any values of 2 or higher. When it finds a 2 or higher, I want it to find the corresponding name in the SAME ROW in column E, and of course the corresponding price in the SAME ROW in column F. Then I would like only those qualifying people "with value of 2 or higher" to be listed in order from highest price to lowest price in Column A, and B.

COLUMNS

D--------E-----------F---

0-----Mike Bob-----$52.65

1-----Dave Jon-----$42.50

2-----Jane Doe-----$37.65

0-----Gary Lon-----$25.50

0-----Joey Saw----$35.65

2-----Mike Jon-----$35.65

1-----Kate Low-----$38.68

2-----John Doe-----$40.00................

I want a cell to return the lowest value in a time series of data that comes AFTER the highest value in the range (so date specific). I have the formula for finding the highest value. The time series range changes on a rolling 1 year basis and I have attached the file. The cells highlighted in orange are the ones that need calculating.

View 2 Replies View Relatedi have a table with multiple codes and quantities along with other info in it. What i need is to take each MATERIAL CODE and its lowest quanity and transfer it to another cell. I have tried using DMIN but can not quite get it. It stopped working after the 6th one.

103057300 -800

103057300 -700

103057300 -250

101789050 50

101789050 -70

101789050 -90

So i want to to take the following quanities that are in this font and put them into a separate cell.

i have in the range (Ag1:an1)the names of the months from january- august)in the range (Ag2:An55) ihave numbers in every cell now in every row for example Ag2:An2 i want to find the values less than 50 then i want to write thier month's names in the cells from Ap2:Aw2 i want to do this with every row from row 2 to 55

View 9 Replies View Relatedif it is possiable to do a find function to find the lowest value in a colum and then output that entire row. e.g a list of dates, I need to know what is the oldest date and what row that is for reference.

View 9 Replies View RelatedI have three columns with 1 number in each row. I'm trying to find a number, and if that number does not exist in the 3 columns I would like to find the next smaller number. The numbers have up to 4 decimal places. i.e. 16140.0311.

So for example if a user searches for 15950.012, and that does not exist but 15950.009 does with no numbers in between then the answer returned would be 15950.009. Auto Merged Post Until 24 Hrs Passes;I should probably mention that I would like to insert a new line with the number originally searched for, after the number found.

i.e. search for 15950.012. Not found. 15950.009 next lowest. Insert new line after 15950.009.

I trial tested many another forumla's before posting this.

I'm having a hard time building this simple function see image:

[url]

The formula I need finds the hightest & lowest frequency appears of a number from the index list (index numbers range from 0-9 or if needed changed to 1-50). Along with the hightest/lowest frequency's it needs to also find the second hightest/lowest frequency's i.e.

need result: (9 2) (0 3)

(0 & 3 did not appear so there listed as the lowest frequency's)

index

2 <

2 <

9 <

4

5

6

7

8

9 <

8

7

6

9 <

4

...ect

Im creating a list of cash and carry places to buy drinks but im so clueless on how to go about doing it.

Heres the situation:

In sheet 1 I have a list of Drinks and the prices the shops are selling it for.

I have duplicates of the drinks so say for bacardi i would have one row with one shop with its price and another shop with another price. I cant put it into columns because there is other information such as the quantity the shop sells in one box etc.

On another sheet (sheet2) i have a kind of shopping list. This has all the drinks listed in Sheet 1 as mentioned above but NO DUPLICATES. What i need it to do is find the drink is Sheet 1 and pick the row with the lowest price from the multiple entries and copy that price and shop name over to sheet2.

I hope this is easy to understand. Please let me know if I need to explain some bits again. Im trying my best to figure out how to do this. I have no clue how to do it in Access.

ADVERTISEMENT