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:
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.