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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kaspkasp@gmail.com - 12 Jan 2007 19:18 GMT

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

Reply to this Message

Bernie Deitrick - 12 Jan 2007 20:37 GMT

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

> Hi,
>
[quoted text clipped - 29 lines]
> Thanks in advance,
> Kasp

Reply to this Message

Rate this thread: