# data rearrangement problem

1. ## data rearrangement problem

Hi all,

I have a table of data that were generated during a scientific trial. The data describe the time (in days) at which an effect was observed in patients given a drug. My table looks like this:

 Days post administration Number of patients affected 1 0 2 1 3 3 4 1

I need to rearrange this data to look at the patients as a population so that I can calculate the mean and standard deviation for the time at which they became affected. So I need a table that looks like this:

 Patient Affected on day 1 2 2 3 3 3 4 3 5 4

Any suggestions on how to perform this rearrangement would be much appreciated. I'm stumped.

2. ## Re: data rearrangement problem

 A B 1 Days Qty 2 1 0 3 2 1 4 3 3 5 4 1 6 7 8 Pat Aff 9 1 0 10 2 1 11 3 4 12 4 5 13 5 5

B9=SUMIF(\$A\$2:\$A\$5,"<="&A9,\$B\$2:\$B\$5)
copied down

3. ## Re: data rearrangement problem

I made the tables in the original post manually as examples. My real data set is obviously somewhat larger.

It works like this:

On day 1, 0 patients were affected
On day 2, 1 patient was affected
On day 3, 3 patients were affected
On day 4, 1 patient was affected

In other words:

The first patient was affected on day 2
The second, third and fourth patients were affected on day 3
The fifth patient was affected on day 4

There are only five patients and four days in the data set in this example.

The numbers in column A of my second table are irrelevant. They could just as easily be letters, names or anything else to designate a unique identifier to each patient. E.g.

 patient affected on day Bob 2 Fred 3 Bill 3 John 3 Cletus 4

It doesn't matter, I just need a list.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1