pivot table - combine 2 columns to generate a new 3rd column

[kaspkasp]

Hi,

My excel skills are almost nil and I have a task to do.
I need to calculate "rate" i.e [ (col1/col2)*100 ] based on a grouping
of 'Group'.
Eg.
Group col1 col2
A 1 1
B 0 1
C 1 1
A 0 1
B 0 1
C 0 1
A 1 1

I want my result to show:
Group Rate
A 2/3*100
B 0/3*100
C 1/3*100

Rate is number of col1 (where col1 = 1) divided by number of col2
(agains 1s only) into 100.

I tried pivot tables to group by Group (A,B,C) and can add 2 columns
Count(col1) and count(col2).
Is it possible to add a 3rd column to pivot table like this...
count(col1)/count(col2)*100?

How can I calculate Rate here? I have over 20K records.

Thanks in advance,
Kasp

 

[Bernie Deitrick]

Kasp,

Select your table, use Data / Pivto Table.... and click OK.

Then drag "Group" to the rows area, then "col1" to the data area, set it to "SUM", and the drag
"col2" to the data area, again set to SUM.

Then drag the data button to the top of the table to re-orient the fields. (Make columns of col1
and col2 instead of rows.)

Then use "Pivot Table" (on the PT commandbar), Formulas... Calculated Field. Give it a name, and
in the formula area, use

= col1/col2

(use the insert button to get the references into the formula)

Then click Add. And in the pivot table, select that field, use "Field Settings" select the "Number"
button, and format for percentage, with as many decimals as you need.

And you're done...

HTH,
Bernie
MS Excel MVP