Min / Max

[Nickel]

Hi All

I need to select a min and max value based on a multiple condition. E.g.

Year Level Salary
2010 1 100
2011 2 130
2010 1 90
2011 2 150
2010 1 130
2011 2 200

I need a formula that would return min, max values for 2010 and 2011 separately
Min Max
2010 XX yyy
2011 xx yyy

I have MS Excel 2003.

Many thanks in advance
Niel

 

[JamesW]

Hi there,

Try:

=MAX(IF(A2:A7=2010,C2:C7))

and

=MIN(IF(A2:A7=2010,C2:C7))

Confirm both with CTRL, SHIFT + ENTER - not just ENTER.

 

[Nickel]

Thanks James

My situation is more complex.

I need to be able to choose it for a specifit level. In may example there is only one level in 2010 but what if I had Level 1 and 2.

I need the min and max values for noth.

 

[Dannyh1]

Hi Try:


{=MAX(IF(($A$2:$A$7=2010)*($B$2:$B$7=1),$C$2:$C$7))}

 

[Nickel]

fabulous. it works.

you guys ruuuule

 

[Nickel]

For the same data base i need to count the number specific levels in a specific year. Please help guys. N

 

[Dannyh1]

Try:


=COUNTIF($A$2:$A$7,"2010")

 

[snoopyhr]

I realize your question is already answered, just as an alternative...Maybe you like to give this a try.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Year Level Salary					Year Level Salary		Min	Max	Count
2	2010	1	100			2010	1	100	90	2
3	2011	2	50			2011	2	50	200	3
4	2010	1	90
5	2011	2	150
6	2010	2	130
7	2011	2	200
Sheet1

#VALUE!

 

[snoopyhr]

Ignore my solution the formula is flawed, sorry....