Makes perfect sense, and is possible. Hopefully will have something for you soon, so don't bother doing this manually. I'm curious what this is for, though....
Hi,
I'm new to the forum and a relatively basic user of excel, so apologies if I have posted this in the wrong place or post is a bit confusing...
I am looking for a simple and quick method to populate a column (well, 300 odd columns) with x instances of a fixed value, followed by x intances of another fixed value, and so on for a total of 13 fixed values. In my dataset I have roughly 300 rows (participants) x 13 columns (categories). Each cell represents a percent estimate of people falling within each category (such that each row always sums to 100), and estimates vary across participants. For each participant (row) I would like to sequentially populate a column with x instances (no. instances is ppt's percent estimate for category 1) of the value in the header row, followed by x instances (no. instances is ppt's percent estimate for category 2) of the value in the header row, and so on for all 13 cells, for each ppt. This may make more sense looking at the attached example... In columns M-O I have done this manually for ppts 1-3 to provide a sense of what I am trying to achieve. The fixed values are in the header row A1-K1, and are constant across ppts. So for example, ppt 1 (column m) has 10 instances of 7499.5, followed by 19 instances of 22499.5, and so on. Is there an efficient method to automatically populate a column for each ppt based on these rules?
I hope this makes sense. Any help would be much appreciated.
Thanks,
Rael
Makes perfect sense, and is possible. Hopefully will have something for you soon, so don't bother doing this manually. I'm curious what this is for, though....
Okay, this ain't pretty but it works. Select the range M2 to H101 then put this in the formula bar and press Control + Shift + Enter to array enter the one formula over the entire range.
(You actually don't HAVE to enter this as an array formula, but it probably is more efficient if it IS array entered over the range)Code:=((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),1)))*$A$1+(((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),1)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),2))))*$B$1+(((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),2)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),3))))*$C$1+(((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),3)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),4))))*$D$1+(((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),4)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),5))))*$E$1+(((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),5)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),6))))*$F$1+(((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),6)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),7))))*$G$1+(((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),7)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),8))))*$H$1+(((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),8)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),9))))*$I$1+(((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),9)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),10))))*$J$1+(((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),10)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),11))))*$K$1
Hmmm...for some reason you can't see the entire formula in the code block. I'll try again but this time I'll put some spaces in it (this doesn't affect the formula at all)
Edit: Nope, that doesn't help. So here it is outside of the code block:Code:=((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),1)))*$A$1 + (((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),1)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),2))))*$B$1 + (((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),2)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),3))))*$C$1 + (((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),3)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),4))))*$D$1 + (((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),4)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),5))))*$E$1 + (((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),5)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),6))))*$F$1 + (((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),6)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),7))))*$G$1 + (((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),7)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),8))))*$H$1 + (((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),8)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),9))))*$I$1 + (((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),9)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),10))))*$J$1 + (((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),10)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),11))))*$K$1
=((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),1)))*$A$1 + (((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),1)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),2))))*$B$1 + (((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),2)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),3))))*$C$1 + (((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),3)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),4))))*$D$1 + (((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),4)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),5))))*$E$1 + (((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),5)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),6))))*$F$1 + (((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),6)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),7))))*$G$1 + (((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),7)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),8))))*$H$1 + (((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),8)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),9))))*$I$1 + (((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),9)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),10))))*$J$1 + (((ROW()-1)>SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),10)))*((ROW()-1)<=SUM(INDEX(Data,COLUMN()-COLUMN($L$2),1):INDEX(Data,COLUMN()-COLUMN($L$2),11))))*$K$1
Actually, scrub all that. Put this in cell M2, and array enter (i.e. paste it in the formula bar and push Ctrl+Shift+Enter). THen copy down 100 rows, and then copy across 100 rows.
=INDEX($A$1:$K$1,,IFERROR(MATCH(ROW()-ROW($M$1)-1,MMULT(--(TRANSPOSE(COLUMN($A$2:$K$2))>=(COLUMN($A$2:$K$2))),TRANSPOSE(INDEX($A$2:$K$306,COLUMN()-COLUMN($L$2),))),1),0)+1)
Hi,
Apologies for the late reply, I have been away over the weekend. This works perfectly, I can't thank you enough. I think you have probably saved me a good days work (actually, I have to repeat this for a further 300 in a different data set). An odd request, I know. If you're curious, this is data from a psychology study in which people had to estimate what percentage of the population earn incomes at each of thirteen intervals, essentially producing a histogram of percieved income distribution. I am attempting to calculate approximate Gini coefficients for these estimates. I have a spreadsheet that calculates Gini but it works on the assumption that each observation represents 1 income, so will not work for summary data. There is a method to get straight to Gini from summary data, a bit more efficient, but the formula is way beyond me and the departmental stats whizz won't play ball. This method will produce the same result by treating each 1 percent of people as 1 discreet income. Thanks again, really appreciate the help.
Rael
No problem. ironically, it took me a good day's work! But it's a day in which I learnt a lot, and I'll be able to use this to good effect on other problems. At some stage I'll write up a blog post explaining my approach.
Cheers
Jeff
Bookmarks