首页 | 学习中心 | 注册 登录
返回列表 发帖
bpdulog

UID
222299 
帖子
420 
主题
7 
注册时间
2011-7-2 
最后登录
2015-12-15 
1# 跳转到 » 倒序看帖
打印
tT
发表于 2013-4-19 22:22 | 只看该作者

Excel question, deciles of various number of rows

different, thousands, question
Here is what I am trying to do. I have columns of data, each of varying numbers of rows, that I want to calculate decile averages for. One column could have three hundred rows, which would mean 10 deciles of 30 rows each. I could then manually create an average formula for each of the ten deciles.
The problem is I have thousands of such columns of data, with different numbers of rows. It will take way too long to manually average the deciles. If they were all 30 rows to a decile it would be no problem.
So, does anyone know of a simple solution where one formula will be able to grab the average of the first decile (and 2 - 10 in subsequent formulas), while automatically accounting for whether there are 30 or 50 or whatever rows in that decile. Then I could just copy that formula for the other columns.
收藏 分享

wizofoz

Rank: 2

UID
223399 
帖子
290 
主题
8 
注册时间
2011-7-11 
最后登录
2014-8-6 
2#
发表于 2013-4-19 22:24 | 只看该作者
Your repeated use of the word “deciles” is impressive. No idea what the question is, but I’m impressed none the less.

TOP

spreads

Rank: 2

UID
223397 
帖子
276 
主题
21 
注册时间
2011-7-11 
最后登录
2014-8-7 
3#
发表于 2013-4-19 22:26 | 只看该作者
Right here bro:
=IF(ISERROR(SUM(OFFSET(A$1,(ROW(A1)-1)*10?10,)))/10,”“,SUM(OFFSET(A$1,(ROW(A1)-1)*10?10,))/10)
If the columns are on a sheet starting at A1, just paste this formula beside the columns and drag it to make it apply to all columns
And boom goes the dynamite

TOP

tikfed

Rank: 2

UID
223452 
帖子
418 
主题
12 
注册时间
2011-7-11 
最后登录
2014-8-7 
4#
发表于 2013-4-19 22:27 | 只看该作者
Uh, so if I understand correctly, if column A has 1000 rows, you need ten numbers? That is, the average of the first decile, average of the second decile, … etc.? Is the data sorted or non-sorted? If it’s unsorted, do you want to organize deciles by value or by the position in the column?
In any case, it seems like you should just write a macro to do this. Do you know any VBA?

TOP

dyga

Rank: 2

UID
222309 
帖子
386 
主题
14 
注册时间
2011-7-2 
最后登录
2016-1-18 
5#
发表于 2013-4-19 22:29 | 只看该作者
Use something besides excel.
If that’s not acceptable, you could do something like this:
1) Create a new sheet just for the decile information
2) First column put 1 through 10, for each decile
3) You need to count the number of real entries in each column and then calculate the edges of each decile, so for the first one and decile 1, it would be something like, in cell B1, =small(sheet1!A:A,COUNT(A:A)*$A1/10)
4) after you copy this over, create another sheet (not really necessary), you can put in the 1-10, but not needed
5) in cell B1 (if you have the 1-10 in), type =sumif(sheet1!A:A,” for B2, you could need to do the same thing but subtract what is above
6) You need another sheet to do a similar calculation but for the counts, (replace sumif with countif)
7) Divide the sums from the counts. You might be able to simplify this so you just need one averageif, but I can’t say for sure since I don’t recall if the logical arguments work with more than one statement.
Should come out to what you want.

TOP

RMontgomery

Rank: 2

UID
223450 
帖子
359 
主题
10 
注册时间
2011-7-11 
最后登录
2014-8-7 
6#
发表于 2013-4-19 22:35 | 只看该作者
1/ Insert 10 rows ABOVE all your data.
2/ enter the following ARRAY formula (ctrl-shift-enter) into A1
{=SUM(OFFSET(A$11,0,0,COUNT(A$11:A$65000),1)*(CEILING(RANK(OFFSET(A$11,0,0,COUNT(A$11:A$65000),1),OFFSET(A$11,0,0,COUNT(A$11:A$65000),1))/COUNT(A$11:A$65000)*10,1)=ROW()))/SUM(1*(CEILING(RANK(OFFSET(A$11,0,0,COUNT(A$11:A$65000),1),OFFSET(A$11,0,0,COUNT(A$11:A$65000),1))/COUNT(A$11:A$65000)*10,1)=ROW()))}
3/ Fill down to A10, then fill right
A1:A10 will give you decile averages for col A, B1:B10 for col B…
andd soo on and sooo on

TOP

AnalystForum

Rank: 2

UID
222281 
帖子
261 
主题
59 
注册时间
2011-7-2 
最后登录
2014-6-28 
7#
发表于 2013-4-19 22:37 | 只看该作者
I just saw this query,
Actually you can use function ‘large’
so you insert one column at the left and 51 (1/10 of the maximum rows +1) rows at the top of your data
A2:A51 = 1 to 50
b1 = =COUNT(B52:B7001),
b2 = =IF($A2

TOP

former

UID
222293 
帖子
680 
主题
67 
注册时间
2011-7-2 
最后登录
2016-4-19 
8#
发表于 2013-4-19 22:39 | 只看该作者
Thanks for the help, I think I have a decent idea of how I am going to do this

TOP

返回列表