In this video, I’m going to use Pivot table in Google sheets to answer these questions
based on these data To use Pivot table, make sure there are no
totally empty rows or columns.
Click somewhere in the data Then click Data
Pivot table And Create
You will see the Pivot table editor on the right here.
To find the number of large purchases, I can go to either rows or columns,
click Add, and select Purchase Level
Then go to Values click Add,
and select Purchase Level again.
And you can see here that we have 40 small purchases.
To find the number of customers that paid with credit, I’m going to close these selections.
Go to Rows, and Add Payment Method
And to Value and Add Payment Method again.
And we can see here that 26 customers paid with credit.
Next, we find the number of registered customers’ purchases that are large.
For Rows, I’m going to Add Purchase Level.
For Columns, I’ll add Registration.
And for Values, I can add either Purchase Level or Registration.
I’m going to centre the contents by clicking on these 3 dots and align centre.
And we can see here that there are 16 large purchases by Registered customers.
To find the number of unregistered customers that paid with cash
I’m going to close Purchase Level in Rows and replace it with Payment Method.
Columns is still registration, and I have to ensure that Values is set to either Payment
Method or Registration.
I’m going to select the last 2 rows and align centre.
And there are 22 unregistered customers that paid with cash.
To find the percent of all customers that are unregistered and paid with cash,
we can simply divide 22 by the grand total 133 here.
Or, we can go to values and “show as”: % of grand total.
And we see that 16.54% of all customers are unregistered and paid with cash.
To find the proportion of all customers that are registered and made a medium purchase,
I’m going to change Rows to Purchase level.
Column is already Registration, and I’ll ensure that Values is set to one of them.
So, the proportion is 12.03%.
Next, we find the percent of registered customers whose purchases are large.
Let me first return these values to count by changing Values to default here.
The question is essentially asking that out of these Registered customers, what proportion
made large purchases?
I can simply divide 16 by 44 or I can go to Values and show as: % of column total.
And that is 36.36%.
To find the percent of unregistered customers that paid with mobile, I’m going to change
rows to Payment Method.
And you can already see that we have 100% in row total from the last question.
That is, these are the proportions of Registered and these are of Unregistered.
So, the percent of unregistered customers that paid with mobile is 23.6%.
To find the mean amount spent by unregistered customers, I’m going to close these.
Add registration to rows.
And Add Amount Spent to Values.
The result automatically shows Sums.
I’ll go to Summarize by and select AVERAGE.
And if I want the median, I’ll go to Summarize by, and select MEDIAN.
And that’s it for this video.
Thanks for watching.