How to use pivoting table to do division?

Question

Hi everyone, I need a little help here! Lets say below is a dataset for the study duration of classroom A, i want to find out how many words learned per hour. Thus, it supposed to be Words learned divide by study duration (hours) right?

But the problem is, if you notice, there are a few study slots on a same day. Let's say on 21/3/2023, there are 3 slots there, I want to know the words learned per hour for each day. So i need to add 20+13+9 words divide by 3+2+1hours to know the total words learned per total hour. And in my dataset, some days have 2 study slots like on 24/3/2023 while some has only 1 slot like 25/3/2023. May I ask, what formula or what steps is needed in pivoting table in order to achieve what I wanted?

Answer 1

Hi @kain

For formula, I suggest you try Sumproduct function to have a check.

=SUMPRODUCT(($A$2:$A$7=A2)*($B$2:$B$7=B2)*($D$2:$D$7))/SUMPRODUCT(($A$2:$A$7=A2)*($B$2:$B$7=B2)*($C$2:$C$7))

For Pivot Table, please try following steps.

• Insert a Pivot Table. ( You may choose this Pivot Table > Design > Layout group> Choose 'Do Not Show Substotals' for Substotals and 'Off For Rows and Columns' for Grand Totals.

• Select this Pivot Table > PivotTable Analyze > Caculations group > Fields, Items & Sets > Caculated Field > Create a new field, such as 'Per_Hour' (= SUM('Words Learned' )/SUM('Study Duration')), then click Add.

(Please note, you may choose the existed field to insert to create 'Per_Hour' formula instead of tyrping field name manually.)

• Then we will get the results like below image.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---