Getting % of observations in a pivot subject to a condition

[Mirisage]

Hi Excel Forum,

I have a dataset (pl see tab named “Data” in the attached).

This data set was generated by running a SAS code. And the dataset gets updated in each month via monthly running of SAS code.

I wanted to create the Table 1 found in the tab named “Manual Calculation”.

Table 1 shows the % of accts who has utilized over 90% of their credit limit.

To automate the process, I have created a simple pivot (see tab named “Pivot”).

But pivot doesn’t produce the results I wanted.

Could we create a pivot to produce the same results as in table 1.


(Why I wanted a pivot is this. We have the SAS Add-in feature in our Excel 2010.
So, when we run the SAS code for a new month, a new SAS table is generated. This SAS table in linked to Excel Pivot. When we re-fresh the pivot, then the pivot get updated with the inclusion of the new month’s data).Thanks

Mirisa

[oeldere]

You have to explain how you manualy counted this values

Table 1: Percentage of accts who has utilized over 90% of their credit limit
Province 201504 201505
Cali 14,3 14,3
New_Mex 0,0 14,3
Tex 0,0 14,3
Total 14,3 42,9

[Mirisage]

Hi Oeldere,

Since this is just a small dataset (my original dataset is massive), I have counted one observation-by-observation.

Thanks

Mirisa

[Mirisage]

Hi Again Oeldere,

Now only I realized what you have asked.

Table 1 should be revised like below.

Table 1: Percentage of accts who has utilized over 90% of their credit limit
Province 201504 201505
Cali 50% 50%
New_Mex 0,0 50%
Tex 0,0 33%
Total 14,3% 42,9%