Cohort analysis in pivot table

giddyup43

New Member
Joined
Dec 3, 2015
Messages
29
Hi.
I figured out how to create a cohort analysis in a pivot table to show how many customers left each month, based on which month they started.
The table looks something like this:
Grand Total
ABCDEF
1MayJuneJulyAugustGrand Total
2May26534144
3June1051214256
4July25162389
5August183249

<tbody>
</tbody>

<tbody>
</tbody>
F2 is how many new people shopped in May. B2 is from the people who shopped in may the first time, how many dropped out in may. C2 is from the people who shopped in may the first time, how many dropped out in June...and so on.
C3 is from the people who shopped in june the first time, how many dropped out in june. D3 is from the people who shopped in june the first time, how many dropped out in July...and so on.

My question is, how can I make that the values in columns B:E should be how many people were left in that month (which is f2-b2) Cell b2 would be 118 (144-26). That number as a percentage would be my rate of retention. (now it just shows me a number of people who left)

If it helps, my pivot table is set up like this:
Filters: City
Rows: Month of First Order
Columns: Month of Last Order
Values: Distinct count of Email Address

Can you help me with this?
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

giddyup43

New Member
Joined
Dec 3, 2015
Messages
29
Hi. I'm bumping this question.
I would really appreciate if someone could help me with this.
If you need me to clarify anything, please let me know.
Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,638
Members
414,398
Latest member
dhune

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top