Splitting tables

Hi Excel Forum

I'm having a problem concerning splitting up cells. On the attached file, there is a short description of my problem as well.

I have a long sample of stochastic data which I would like to divide into n=2, n=3 or any other number of rows. Is there a function that can do so?
The question is easier understandable if looking at the data.

Really hope you can help me!! Google haven't been able to do so..


Best regards

Martin

I'm just going out now, but I'll get back to you later on if no-one else has (really, just marking this to come back to it).

Pete

It's a given assignment in statistics.

n=2 means dividing the first coloumn into 2 observations
n=3 means diving into 3.

Example n=2:
Coloumn B (16,15.9,16,16,16,15)
.. should be splitted into
Coloumn D (16,16,16) and Coloumn E (15.9,16,15)

Example if n=3:
Coloumn B (16,15.9,16,16,16,15)
.. should be splitted into
Coloumn D (16,16) and Coloumn E (15.9,16) and Coloumn F (16,15)


Looking for a function that can do so..

Okay, let's take it from scratch..

Based on an assigment in statistics I am requesting a formula to splitting up the table. I have already shown the method to do the assignment, however what I am asking for is if there is a function in excel, so I won't have to plot all the numbers manually (waste of time).

The n value I have been talking about indicates the number of observations the sample needs to be separated into. These numbers are given, and is not influenced by any other numbers. Given in a message box..


Just for clarification - I'm not asking you to do the assignment. I'm simply asking whether there is a function which can plot the numbers, so I don't have to waste time on doing so in the future.
Parts of the assignment has been attached as well as an updated version, displaying examples of n=2 and n=3.


Hope someone can/will help!

There isn't just a single function that can do this for you - you will need a combination of functions to compile in a formula which can then be copied across and down to give you the splits you require.

I would suggest using cell C1 to enable you to select the number of columns you want to split your data into. You could have data validation applied to this cell, such that you can only select from a distinct set of column numbers (2, 3, 4, 5, 6, 10, 12, 15, as suggested in your literature).

Then in column D you could have a formula which adjusts for the number of rows of data that you will end up with in your split table. For example, put the label "Number" in D1 and this formula in D2:

=IF((ROW()-1)*$C$1>COUNTA($A:$A),"",ROW()-1)

Copy this formula down (eg to D40), and then see the effect by choosing different numbers in C1.

Then you need one formula in E2 which can be copied down and across to give you the split of data that you require. I won't give you this formula per se, but I will indicate to you which functions you will need to make use of, so that you can find out what they do and how you might put them together:

COLUMNS(...), COUNTA(...), IF(...), INDEX(...), MOD(...), ROW()

Let us know how you get on.

Hope this helps.

Pete