I have the following table, which shows the approval rate of our deposits for every hour of the day. For example the approval rate at 4 AM was 66.67, but the running average is 58.93% (100+28+50+50+66.67)/5. How do I add this value, which I am showing in a chart, that shows both approved, rejected and the running average? For context, the approved % is calculated by the amount of transactions that were approved at a specific hour.
[TABLE="width: 290"]
<tbody>[TR]
[TD]Processor[/TD]
[TD]Approved[/TD]
[TD]Rejected[/TD]
[/TR]
[TR]
[TD]Hour[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD="align: right"]100.00%[/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]28.57%[/TD]
[TD="align: right"]71.43%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]50.00%[/TD]
[TD="align: right"]50.00%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]50.00%[/TD]
[TD="align: right"]50.00%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]66.67%[/TD]
[TD="align: right"]33.33%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]50.00%[/TD]
[TD="align: right"]50.00%[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]75.00%[/TD]
[TD="align: right"]25.00%[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: right"]75.00%[/TD]
[TD="align: right"]25.00%[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]100.00%[/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"]75.00%[/TD]
[TD="align: right"]25.00%[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"]86.67%[/TD]
[TD="align: right"]13.33%[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD="align: right"]77.78%[/TD]
[TD="align: right"]22.22%[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD="align: right"]37.50%[/TD]
[TD="align: right"]62.50%[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD="align: right"]63.64%[/TD]
[TD="align: right"]36.36%[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD="align: right"]100.00%[/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]70.18%[/TD]
[TD="align: right"]29.82%[/TD]
[/TR]
</tbody>[/TABLE]
The average approval rate is only based on the approved %, but I can't get it to work, and on the pivot table, I would like that running average to be a 3rd column next to the Rejected column.
The ideal pivot table would look like this:
[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 64"]Hour[/TD]
[TD="class: xl63, width: 64"]Approved[/TD]
[TD="class: xl63, width: 64"]Rejected[/TD]
[TD="class: xl63, width: 64"]App Rate[/TD]
[/TR]
[TR]
[TD="class: xl65"]0[/TD]
[TD="class: xl66, align: right"]100.00%[/TD]
[TD="class: xl66, align: right"]0.00%[/TD]
[TD="class: xl66, align: right"]100.00%[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl66, align: right"]28.57%[/TD]
[TD="class: xl66, align: right"]71.43%[/TD]
[TD="class: xl66, align: right"]28.57%[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl66, align: right"]50.00%[/TD]
[TD="class: xl66, align: right"]50.00%[/TD]
[TD="class: xl66, align: right"]50.00%[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl66, align: right"]50.00%[/TD]
[TD="class: xl66, align: right"]50.00%[/TD]
[TD="class: xl66, align: right"]50.00%[/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl66, align: right"]66.67%[/TD]
[TD="class: xl66, align: right"]33.33%[/TD]
[TD="class: xl66, align: right"]66.67%[/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl66, align: right"]50.00%[/TD]
[TD="class: xl66, align: right"]50.00%[/TD]
[TD="class: xl66, align: right"]50.00%[/TD]
[/TR]
[TR]
[TD="class: xl65"]6[/TD]
[TD="class: xl66, align: right"]75.00%[/TD]
[TD="class: xl66, align: right"]25.00%[/TD]
[TD="class: xl66, align: right"]75.00%[/TD]
[/TR]
[TR]
[TD="class: xl65"]7[/TD]
[TD="class: xl66, align: right"]75.00%[/TD]
[TD="class: xl66, align: right"]25.00%[/TD]
[TD="class: xl66, align: right"]75.00%[/TD]
[/TR]
[TR]
[TD="class: xl65"]8[/TD]
[TD="class: xl66, align: right"]100.00%[/TD]
[TD="class: xl66, align: right"]0.00%[/TD]
[TD="class: xl66, align: right"]100.00%[/TD]
[/TR]
[TR]
[TD="class: xl65"]9[/TD]
[TD="class: xl66, align: right"]75.00%[/TD]
[TD="class: xl66, align: right"]25.00%[/TD]
[TD="class: xl66, align: right"]75.00%[/TD]
[/TR]
[TR]
[TD="class: xl65"]10[/TD]
[TD="class: xl66, align: right"]86.67%[/TD]
[TD="class: xl66, align: right"]13.33%[/TD]
[TD="class: xl66, align: right"]86.67%[/TD]
[/TR]
[TR]
[TD="class: xl65"]11[/TD]
[TD="class: xl66, align: right"]77.78%[/TD]
[TD="class: xl66, align: right"]22.22%[/TD]
[TD="class: xl66, align: right"]77.78%[/TD]
[/TR]
[TR]
[TD="class: xl65"]12[/TD]
[TD="class: xl66, align: right"]37.50%[/TD]
[TD="class: xl66, align: right"]62.50%[/TD]
[TD="class: xl66, align: right"]37.50%[/TD]
[/TR]
[TR]
[TD="class: xl65"]13[/TD]
[TD="class: xl66, align: right"]63.64%[/TD]
[TD="class: xl66, align: right"]36.36%[/TD]
[TD="class: xl66, align: right"]63.64%[/TD]
[/TR]
[TR]
[TD="class: xl65"]14[/TD]
[TD="class: xl66, align: right"]100.00%[/TD]
[TD="class: xl66, align: right"]0.00%[/TD]
[TD="class: xl66, align: right"]100.00%[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 290"]
<tbody>[TR]
[TD]Processor[/TD]
[TD]Approved[/TD]
[TD]Rejected[/TD]
[/TR]
[TR]
[TD]Hour[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD="align: right"]100.00%[/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]28.57%[/TD]
[TD="align: right"]71.43%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]50.00%[/TD]
[TD="align: right"]50.00%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]50.00%[/TD]
[TD="align: right"]50.00%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]66.67%[/TD]
[TD="align: right"]33.33%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]50.00%[/TD]
[TD="align: right"]50.00%[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]75.00%[/TD]
[TD="align: right"]25.00%[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: right"]75.00%[/TD]
[TD="align: right"]25.00%[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]100.00%[/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"]75.00%[/TD]
[TD="align: right"]25.00%[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"]86.67%[/TD]
[TD="align: right"]13.33%[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD="align: right"]77.78%[/TD]
[TD="align: right"]22.22%[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD="align: right"]37.50%[/TD]
[TD="align: right"]62.50%[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD="align: right"]63.64%[/TD]
[TD="align: right"]36.36%[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD="align: right"]100.00%[/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]70.18%[/TD]
[TD="align: right"]29.82%[/TD]
[/TR]
</tbody>[/TABLE]
The average approval rate is only based on the approved %, but I can't get it to work, and on the pivot table, I would like that running average to be a 3rd column next to the Rejected column.
The ideal pivot table would look like this:
[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 64"]Hour[/TD]
[TD="class: xl63, width: 64"]Approved[/TD]
[TD="class: xl63, width: 64"]Rejected[/TD]
[TD="class: xl63, width: 64"]App Rate[/TD]
[/TR]
[TR]
[TD="class: xl65"]0[/TD]
[TD="class: xl66, align: right"]100.00%[/TD]
[TD="class: xl66, align: right"]0.00%[/TD]
[TD="class: xl66, align: right"]100.00%[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl66, align: right"]28.57%[/TD]
[TD="class: xl66, align: right"]71.43%[/TD]
[TD="class: xl66, align: right"]28.57%[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl66, align: right"]50.00%[/TD]
[TD="class: xl66, align: right"]50.00%[/TD]
[TD="class: xl66, align: right"]50.00%[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl66, align: right"]50.00%[/TD]
[TD="class: xl66, align: right"]50.00%[/TD]
[TD="class: xl66, align: right"]50.00%[/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl66, align: right"]66.67%[/TD]
[TD="class: xl66, align: right"]33.33%[/TD]
[TD="class: xl66, align: right"]66.67%[/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl66, align: right"]50.00%[/TD]
[TD="class: xl66, align: right"]50.00%[/TD]
[TD="class: xl66, align: right"]50.00%[/TD]
[/TR]
[TR]
[TD="class: xl65"]6[/TD]
[TD="class: xl66, align: right"]75.00%[/TD]
[TD="class: xl66, align: right"]25.00%[/TD]
[TD="class: xl66, align: right"]75.00%[/TD]
[/TR]
[TR]
[TD="class: xl65"]7[/TD]
[TD="class: xl66, align: right"]75.00%[/TD]
[TD="class: xl66, align: right"]25.00%[/TD]
[TD="class: xl66, align: right"]75.00%[/TD]
[/TR]
[TR]
[TD="class: xl65"]8[/TD]
[TD="class: xl66, align: right"]100.00%[/TD]
[TD="class: xl66, align: right"]0.00%[/TD]
[TD="class: xl66, align: right"]100.00%[/TD]
[/TR]
[TR]
[TD="class: xl65"]9[/TD]
[TD="class: xl66, align: right"]75.00%[/TD]
[TD="class: xl66, align: right"]25.00%[/TD]
[TD="class: xl66, align: right"]75.00%[/TD]
[/TR]
[TR]
[TD="class: xl65"]10[/TD]
[TD="class: xl66, align: right"]86.67%[/TD]
[TD="class: xl66, align: right"]13.33%[/TD]
[TD="class: xl66, align: right"]86.67%[/TD]
[/TR]
[TR]
[TD="class: xl65"]11[/TD]
[TD="class: xl66, align: right"]77.78%[/TD]
[TD="class: xl66, align: right"]22.22%[/TD]
[TD="class: xl66, align: right"]77.78%[/TD]
[/TR]
[TR]
[TD="class: xl65"]12[/TD]
[TD="class: xl66, align: right"]37.50%[/TD]
[TD="class: xl66, align: right"]62.50%[/TD]
[TD="class: xl66, align: right"]37.50%[/TD]
[/TR]
[TR]
[TD="class: xl65"]13[/TD]
[TD="class: xl66, align: right"]63.64%[/TD]
[TD="class: xl66, align: right"]36.36%[/TD]
[TD="class: xl66, align: right"]63.64%[/TD]
[/TR]
[TR]
[TD="class: xl65"]14[/TD]
[TD="class: xl66, align: right"]100.00%[/TD]
[TD="class: xl66, align: right"]0.00%[/TD]
[TD="class: xl66, align: right"]100.00%[/TD]
[/TR]
</tbody>[/TABLE]
Last edited: