Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

2 Answers 2

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.