Quartile Averages to match Excel.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Quartile Averages to match Excel.

Author

Topic

leahsmart
Posting Yak Master

133 Posts

Posted - 2008-11-05 : 06:38:05

Hey,

I have a page which offers the option to plot the lower quartile average or the upper quartile average. I have been working this out by taking the average and then average everything that falls below that for the lower, and everything above for the upper.

Recently one of our customers came to us and said this was all wrong, and it should do it the way excel does.

I have read some of excels help but it does not define it's calculations.

Anyone have any idea on this?

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-05 : 14:28:44

I think this is interesting that's why I push it on top again.
I will have a look to bring up the calculation in a.) the easy way and b.) the excel way.
And after this it is interesting to make a solution for T-SQL.

But it may take a little while...

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-06 : 05:11:03

This uses the formula given at:
http://mathforum.org/library/drmath/view/60969.html
which is meant to be Excel formula but the results are not exactly the same.
Maybe this will point some-one else in right direction.

Declare @n int
declare @L int
declare @U int

set @n= (SELECT count(columnname) FROM tablename)

set @L = (select .25 * (@n+3))
Select @L as LowerQ

set @U = (select .25 *( @L * @n+1) )
Select @U as UpperQ

Select columnname
from
(select columnname,row_number()over (order by columnname) as ranked
from tablename) as e
where ranked = @U

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-06 : 05:18:14

Use the new NTILE() function available in SQL Server 2005.

E 12°55'05.63"
N 56°04'39.26"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-06 : 05:57:11

Peso - Can you give example where Ntile gives same results as excel for different values?

I used following which does match some-times but not always exactly.

Select avg(convert(decimal(8,2),columnName)) as LowerQ from
(

SELECT columnName
,NTILE(2) OVER(ORDER BY columnName ASC) AS 'Quartile'

FROM TableName) as B
where Quartile =1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-06 : 08:51:41

[code]SELECT TOP 1
(COUNT(*) OVER (PARTITION BY (SELECT 0)) + 3.0E) / 4.0E AS lowQ,
(3.0E * COUNT(*) OVER (PARTITION BY (SELECT 0)) + 1.0E) / 4.0E AS highQ
FROM (
SELECT 1 AS d UNION ALL
SELECT 2 UNION ALL
SELECT 6 UNION ALL
SELECT 4 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 5 UNION ALL
SELECT 11 UNION ALL
SELECT 7 UNION ALL
SELECT 3 UNION ALL
SELECT 8
) AS d[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-06 : 09:02:03

[code]SELECT MIN(lowQ) AS lowQ,
AVG(CASE WHEN recID <= lowQ THEN 1.0E * d ELSE NULL END) AS lowAvg,
MIN(highQ) AS highQ,
AVG(CASE WHEN recID >= lowQ THEN 1.0E * d ELSE NULL END) AS highAvg
FROM (
SELECT (COUNT(*) OVER (PARTITION BY (SELECT 0)) + 3.0E) / 4.0E AS lowQ,
(3.0E * COUNT(*) OVER (PARTITION BY (SELECT 0)) + 1.0E) / 4.0E AS highQ,
d,
ROW_NUMBER() OVER (ORDER BY d) AS recID
FROM (
SELECT 1 AS d UNION ALL
SELECT 2 UNION ALL
SELECT 6 UNION ALL
SELECT 4 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 5 UNION ALL
SELECT 11 UNION ALL
SELECT 7 UNION ALL
SELECT 3 UNION ALL
SELECT 8
) AS d
) AS w[/code]

E 12°55'05.63"
N 56°04'39.26"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-06 : 09:49:03

This does match Excel for other values I tested. E.g
5,10,15,20,25,30,35,40,45,50,55
Excel: lower quartile=17.5
Peso's query: lowerQ=3.5

leahsmart
Posting Yak Master

133 Posts

Posted - 2008-11-06 : 09:58:41

Hey,

Thanks for all the replies, I shall have a good read through this shortly, have some urgent work come up which is keeping me away :D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources