Conditional Minimum and Maximum

[Nintynuts]

Hi , I use countIF, averageIF and sumIF a lot, and I want to do the same with Min and Max (also Mean and Median if possible), how would I do this?

Example:
Day Rating Month Year
1 5 Jan 2007
2 3 Jan 2007
3 4 Jan 2007
4 2 Jan 2007
1 1 Feb 2007
2 5 Feb 2007
3 5 Feb 2007

Minimum Rating for January is 2
Minimum Rating for 2007 is 1

The formula will be placed in line with the data and be sortable as with averageIF. (=averageIF(C:C,C1,B:B) would give me the average Rating for January, sorting this gives me months in order of the average rating)

(please note this is a contrived example to show what I mean, I don't really rate days )

 

[jeffreybrown]

How about...

Excel Workbook
	A	B	C	D	E	F
1	Day	Rating	Month	Year	*	*
2	1	5	Jan	2007	*	*
3	2	3	Jan	2007	*	2
4	3	4	Jan	2007	*	1
5	4	2	Jan	2007	*	*
6	1	1	Feb	2007	*	*
7	2	5	Feb	2007	*	*
8	3	5	Feb	2007	*	*
Sheet1

 

[barry houdini]

You can use an "array formula" such as

=MAX(IF(C:C=C1,B:B))

confirmed with CTRL+SHIFT+ENTER

replace MAX with MIN or MEDIAN

 

[Nintynuts]

Ok, thanks, it works, but it takes about a minute to work it out on my quad core processor with my dataset. is there a more straight forward way for it to do it using the same processes as the afore mentioned functions (as they're instant)

 

[T. Valko]

Don't use entire columns as range references if you don't have to. Use the smallest range that you can.

If your data is sorted or grouped together as is shown in your posted sample then use a formula like this:

F2 = some month like Jan

=MEDIAN(INDEX(Rating,MATCH(F2,Month,0)):INDEX(Rating,MATCH(F2,Month,0)+COUNTIF(Month,F2)-1))