Calculating %ages in Data|Subtotal rows
Posted by Steve on December 04, 2001 9:07 AM
I have an Excel 97 worksheet of 100 rows and 4 columns. Column A has descriptions.
Column B has # attempts, C has # successes and D has a % success rate (C/B).
I want to use the Data|Subtotals command to sum columns B & C, but I would like
to have included in the Subtotals line, the % (Subtotal C/Subtotal B).
Does anyone know how to do this without a macro?
Posted by Mark W. on December 04, 2001 9:25 AM
Let's assume that your data in A1:D6 looks like this...
{"Description","Attempts","Successes","%"
;"ABC",10,3,0.3
;"ABC",20,4,0.2
;"ABC",30,8,0.266666666666667
;"ABC",40,12,0.3;"ABC",50,23,0.46}
1. Insert a Sum subtotal for 'Attempts' and
'Successes' for each change in 'Description'
2. Apply and Custom AutoFilter on Column where
'Description' ends with "Total".
3. Enter the formula, =IF(B7,C7/B7,""), into cell
D7 and Copy Down.
