# 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?

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

View 9 RepliesADVERTISEMENT

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

Copyrights 2005-15 www.BigResource.com, All rights reserved