Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I hope someone can help me with this Excel problem, otherwise I'll need to buy another 3rd part control to handle the Pivot problem we've encountered. I know it's somewhat off-topic, but hopefully someone can/will help.

We have raw data that includes the following type of data:

ID      Date         Type
1       11.05.2009    0
2       12.05.2009    0
3       13.05.2009    1
4       14.05.2009    0
5       15.05.2009    0

As you can see, those five rows are from the same week.

We've organized the pivot data so that it groups by week, and we'd like to configure either a formula, calculated field, or whatnot to calculate that 1 out of 5 rows has a 1.

In other words, since there is 1 1'er, and 5 in total, we'd like to have a field that says "20%".

However, we can't figure out how to trick Excel to count the number of rows that went into a pivot cell, and sum the same rows, and thus calculate SUM/COUNT.

It seems our calculated field is done pr. row, and thus useless to us.

Basically, we'd like this:

      Percentage
Week
 19       0%
 20      20%     <-- this is the week from the data above
 21       0%

Adding a calculated field that has this formula:

SUM(Type)/COUNT(Type)

does not produce the right data, we're just getting a 0 or 1 pr. row, basically it looks like Excel does this:

ID      Date         Type     (Calc)
1       11.05.2009    0         0    (0/1)
2       12.05.2009    0         0    (0/1)
3       13.05.2009    1         1    (1/1)
4       14.05.2009    0         0    (0/1)
5       15.05.2009    0         0    (0/1)

Does anyone understand my problem? Does anyone have any hints?

It seems DevExpress Pivot control handles this, but we'd like to avoid this for the time being, if possible.

share|improve this question

migrated from stackoverflow.com Aug 4 '10 at 2:56

This question came from our site for professional and enthusiast programmers.

up vote 3 down vote accepted

If you add a 'Helper' column to your data titled Count. Enter the number 1 into the Count column cells for each row of data you have.

ID      Date         Type   Count
1       11.05.2009    0      1
2       12.05.2009    0      1
3       13.05.2009    1      1
4       14.05.2009    0      1
5       15.05.2009    0      1

Create the pivot table and insert a calculated field with the formula of =Type/Count

In the pivot table format the calculated field as a percentage.

Based on you sample data you should get this:

Week   Percentage
 20      20%
share|improve this answer
    
I'll test this tomorrow when I'm back at the office. – Lasse V. Karlsen May 13 '09 at 16:06
    
Thank you, that worked splendidly. I guess we tried to overcomplicate things by trying to do the SUM/COUNT part ourselves. – Lasse V. Karlsen May 14 '09 at 6:56

As an addendum to the answer above. Use the WEEKNUM function in the last column. That will allow you to group weekly data together.

share|improve this answer
    
We already have that, I simplified the problem data, perhaps too much. – Lasse V. Karlsen May 13 '09 at 16:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .