Lowest/Highest Number Cells

[audrey]

Hello,

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)

Thank you in advance

Audrey

 

[audrey]

but this is already in the formula, I could not find the difference with the first one you wrote

 

[Richard Schollar]

The red 2 shows which numeric value you want ie 35 for Item1 would be 1, 30 for Item2 would be 2, 16 for Item3 would be 3.

=LARGE(INDEX($B$3:$M$5,0,MATCH(C$14,$B$1:$M$1,0)),2)

It's just a standard Large formula - the INDEX/MATCH within just directs the Large to the particular column it sources its values from.

Make sense? Have I understaood what you wanted?

 

[audrey]

yes I got tthat part and changed the formula accordingly. but my question was that, this formula you provided writes the cell, the item name (e.g item1) which was what I wanted initially. Thank you for that

But I also want to fill the next cell nearby the corresponding number that this item1 refers to.

for instance item1 (formula works) and 35 (the formula I am requesting) as it s the biggest line item.

 

[Richard Schollar]

Which is what I gave:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		January	February	March	April	May	June	July	August	September	October	November	December
2		10	15	20	35	17	19	81	27	56	41	11	36
3	Item1	3	7					35
4	Item2	2	5					30
5	Item3	5	3					16
6
7
8
9
10
11
12
13			Best Month
14			July	Value		January	Value
15		1	Item1	35		Item3	5
16		2	Item2	30		Item1	3
17		3	Item3	16		Item2	2
Sheet2

Excel 2003

Cell Formulas
Range	Formula
C15	=INDEX($A$3:$A$5,MATCH(LARGE(INDEX($B$3:$M$5,0,MATCH(C$14,$B$1:$M$1,0)),$B15),INDEX($B$3:$M$5,0,MATCH(C$14,$B$1:$M$1,0)),0))
C16	=INDEX($A$3:$A$5,MATCH(LARGE(INDEX($B$3:$M$5,0,MATCH(C$14,$B$1:$M$1,0)),$B16),INDEX($B$3:$M$5,0,MATCH(C$14,$B$1:$M$1,0)),0))
C17	=INDEX($A$3:$A$5,MATCH(LARGE(INDEX($B$3:$M$5,0,MATCH(C$14,$B$1:$M$1,0)),$B17),INDEX($B$3:$M$5,0,MATCH(C$14,$B$1:$M$1,0)),0))
D15	=LARGE(INDEX($B$3:$M$5,0,MATCH(C$14,$B$1:$M$1,0)),$B15)
D16	=LARGE(INDEX($B$3:$M$5,0,MATCH(C$14,$B$1:$M$1,0)),$B16)
D17	=LARGE(INDEX($B$3:$M$5,0,MATCH(C$14,$B$1:$M$1,0)),$B17)
F15	=INDEX($A$3:$A$5,MATCH(LARGE(INDEX($B$3:$M$5,0,MATCH(F$14,$B$1:$M$1,0)),$B15),INDEX($B$3:$M$5,0,MATCH(F$14,$B$1:$M$1,0)),0))
F16	=INDEX($A$3:$A$5,MATCH(LARGE(INDEX($B$3:$M$5,0,MATCH(F$14,$B$1:$M$1,0)),$B16),INDEX($B$3:$M$5,0,MATCH(F$14,$B$1:$M$1,0)),0))
F17	=INDEX($A$3:$A$5,MATCH(LARGE(INDEX($B$3:$M$5,0,MATCH(F$14,$B$1:$M$1,0)),$B17),INDEX($B$3:$M$5,0,MATCH(F$14,$B$1:$M$1,0)),0))
G15	=LARGE(INDEX($B$3:$M$5,0,MATCH(F$14,$B$1:$M$1,0)),$B15)
G16	=LARGE(INDEX($B$3:$M$5,0,MATCH(F$14,$B$1:$M$1,0)),$B16)
G17	=LARGE(INDEX($B$3:$M$5,0,MATCH(F$14,$B$1:$M$1,0)),$B17)

 

[audrey]

thank you richard, you just made my day