Percentage Question

[robcarr]

Dear All,

I am creating a pivot table of the top 5 reasons for people calling us, I have the pivot table showing only the top 5 reasons, I have then created a formula to count how many cases we have and then another formula to calculate the % of each one of the top 5 against the overall cases logged,

Here is coding for this bit.

Range("h1&quot.Value = "=COUNTA(A2:A65536)" 'counts all the records
Range("h5&quot.Value = "=IF(E5=""Grand Total"","""",IF(AND _(E5>""*total"",G5>0),G5/$H$1,"""&quot)" ' does the count for each total line
Range("h5&quot.Copy
Range("h6:h100&quot.PasteSpecial xlPasteFormulas ' copies and paste's formula.

Once the formula is in place it will only count column g values that have Total at the end of column e, except for the grand total.

What I now need to do is count the values that make up the total for the % above, and calculate as a % against the total for each individual top 5, the problem I have is that each top 5 record could have either 1 - 20 categories, and I don't know where the total line will be is there a way to do this using coding as I can't find out a the formulas to do this.
a brief example of how the sheet looks
reasons 15 ' cases logged
reason 1 5
reason 2 6
reasons total 11 73.33%

Thanks for any suggestions and help, I have looked at field settings in the pivot table but I am unsure of this.

Thanks


Thanks Rob.

 

[Zathras]

You lost me when you said 1-20 categories. Can't you just leave category out of the pivot table and count (i.e. pivot) only by the reason code?

BTW, if you haven't already done so, take a look at the worksheet function LARGE(). It should help you with this project to find the top 5 reasons (after you summarize by reason code).

 

[robcarr]

Zathras,

Thanks for the response, I am still no further on this.

I will try to explain more clearly.

for example:-
I have range a1:a20 filled with numbers, range a21 is a sum of those numbers, i then have a blank cell and then another range of numbers a23:a38 sum of those in range a39, and range a40 is the sum of a1:a20 + a23:a38(the overall total).
How can I show the value of a1 in b1 as a % of a21, when a21 total position could change depending on how many numbers are in the range it is calculating.

I hope this has explained it a little bit.

Thanks Rob.


Thanks Rob.

 

[Zathras]

Sorry, I still can't see the big picture. The formula in B1 of course is [blue]

 =A1/$A$21%

[/color] and copy down, but I'm sure you already knew this.

The original post was about a pivot table. You can probably use VBA to do what you need, but I can't help you yet since you have not described the situation completely. (I still don't know where "categories" fit in, for example.)

BTW, have you explored the "Options" available for a pivot table field? Among the options is "Show data as:" which offers (among other choices) "% of total." I was able to create a pivot table that looks something like this (random numbers):

                  Data
reason  category  Count of casenum  % of Total
      1        1                 1        3.2%
               3                 1        3.2%
               4                 1        3.2%
               5                 1        3.2%
               6                 1        3.2%
1 Total                          5       16.1%
      2        2                 5       16.1%
               3                 2        6.5%
               4                 3        9.7%
               5                 3        9.7%
               7                 1        3.2%
2 Total                         14       45.2%
      3        2                 1        3.2%
               3                 5       16.1%
               4                 2        6.5%
               6                 2        6.5%
3 Total                         10       32.3%
      4        5                 1        3.2%
               6                 1        3.2%
4 Total                          2        6.5%
Grand Total                     31      100.0%

I can only guess that this may be close to what you want.

 

[robcarr]

i thought I could use the way you have described in the pivot table above but I need to do very similar to this but this shows each % as a % of the grand total, I need the sheet to show the % of each reason total(your example), so each category in your table would be 20% of the reason total(for reason 1).

I am using a pivot table, but my last post was just trying to make it easier with the way I was expalining it.

Hope this is making sense.

Thanks for all your help on this so far.

Thanks Rob.

 

[Zathras]

Then try moving "Reason" to the "Column" area, leaving "Category" in the "Row" area.

Change the name "Count of casenum" to "Cases" to allow the column widths to be narrower.