Results 1 to 6 of 6

20080627, 18:12 #1
 Join Date
 Jan 2001
 Location
 Ephrata, Washington, USA
 Posts
 683
 Thanks
 0
 Thanked 0 Times in 0 Posts
Statistical calculations with ranges (2003)
We have a worksheet with data collected every hour of every day for the last 6 years. The data collects a low value and a high value for each hour. We want to show the average for the 8 highest values each day and the average for the 8 lowest values for the day. And, then we need to show the difference between those 2 averages.
Date Hour Ending Low High
1/1/2003 100 $22.00 $25.00
1/1/2003 200 $22.00 $25.00
1/1/2003 300 $22.00 $25.00
Any ideas (not using code unless there is no other way)?

20080627, 18:37 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Statistical calculations with ranges (2003)
Assume you have (for example) the date of interest in F2.
In G2 you can calculate the average of the 8 smallest values of LOW column by entering the array formula (confirm with ctrlshiftenter):
=AVERAGE(SMALL(IF($A$2:$A$2500=F2,$C$2:$C$2500),RO W(INDIRECT("1:8"))))
In H2 you can calculate the average of the 8 largest values of HIGH column by entering the array formula (confirm with ctrlshiftenter):
=AVERAGE(LARGE(IF($A$2:$A$2500=F2,$D$2:$D$2500),RO W(INDIRECT("1:8"))))
In I2 you can calculate the difference:
=H2G2
Adjsut the ranges as needed.
Steve

20080707, 03:06 #3
 Join Date
 Jan 2001
 Location
 Ephrata, Washington, USA
 Posts
 683
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Statistical calculations with ranges (2003)
I'm doing something wrong, Steve. I am getting a zero. I think I've adjusted my formula wrong. Can you take a look and tell me what I'm doing wrong?
Thank you!

20080707, 03:13 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Statistical calculations with ranges (2003)
Steve's idea was that you would enter the date for which you want the averages in cell F2. Since you already have other data in column F, you should enter the date in another cell and use that in the formula.
And instead of I2:I50925, you should refer to the column containing the Low values.

20080707, 03:25 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Statistical calculations with ranges (2003)
I have attached a small sample workbook with dummy data and formulas.

20080707, 08:17 #6
 Join Date
 Jan 2001
 Location
 Ephrata, Washington, USA
 Posts
 683
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Statistical calculations with ranges (2003)
Thank you! That makes perfect sense now (of course). It worked like a charm!