Calculating Certain Numbers

**mattpritch** · 10-14-2009, 06:43 PM

I am looking to make a calculation, but can't figure out how.

Here is a sample data range:

Age Best Time Avg Time
5--------12------------13
6--------13------------13
8--------11------------12
6--------etc...
7
8
8
9
8
9
10

I want to calculate the average time of an 8 year old without having to sort the data first. And I'd also like to pull the best time. I know I can use the (MIN) calculation, but I don't know how to write the IF portion of the calculation, so that it pulls only from the age range I specify.

**daddylonglegs** · 10-14-2009, 06:50 PM

You would normally use these sort of array formulas

=MIN(IF(Ages=8,Times))

and identical for average except AVERAGE function in place of MIN

confirm formulas with CTRL+SHIFT+ENTER

Ages represents the range containing ages and Times that containing the times, both should be the same size

**Ron Coderre** · 10-14-2009, 06:53 PM

With this data in A1:C8

Code:

Age	Best	Avg_Best
3	7
10	4
9	2
3	7
4	9
3	7
9	8

This formula returns the average for the age:

Code:

C2: =SUMIF($A$2:$A$10,A2,$B$2:$B$10)/COUNTIF($A$2:$A$10,A2)

Copy that formula down through C8.

Using the above data, the formulas return these values:

Code:

Age	Best	Avg_Best
3	7	7
10	4	4
9	2	5
3	7	7
4	9	9
3	7	7
9	8	5

Is that something you can work with?

**mattpritch** · 10-14-2009, 07:03 PM

That was about the place I was getting stuck. I'm creating a summary sheet, and will be pulling data from other sheets in the workbook.

http://i250.photobucket.com/albums/g...1/Picture2.png
--This is a sample data range (the range will grow much larger, which is the reason I want to avoid sorting)

http://i250.photobucket.com/albums/g...1/Picture1.png
--This is the summary sheet--

**mattpritch** · 10-15-2009, 05:01 PM

I got it to work using a list of averages and using SUMIF and dividing by COUNTIF.

I'm struggling to get the min function to work now. I can't exactly take a long way around it.

Any ideas?

**mattpritch** · 10-15-2009, 05:06 PM

Here is the function I'm using, and it returns a #NUM! error...

=MIN(IF('10 yd shuttle'!A:A=5,'10 yd shuttle'!B:D))

**daddylonglegs** · 10-15-2009, 05:21 PM

You can't use whole columns with that type of formula in Excel 2003. TRy limiting the ranges, e.g.

=MIN(IF('10 yd shuttle'!A2:A100=5,'10 yd shuttle'!B2:D100))