Hi
I apologize if this becomes hard to follow, I am very new to posting in things like this so very well may not give all of the right info or have all of the correct terminology. I use your forum regularly for help as I am learning Excel, but couldn't find a solution to this.
I am trying to create a % within a pivot table that will be based on 2 other fields that are already in the pivot table. One of the columns is a count and the other is a sum, here is what the pivot table looks like. I need the % Closed to be Qty won divided by Qty Quoted so we can show a close rate.
Qty won is a 'sum' field where I have an if formula in the source data saying basically 'if a quote was won, show 1, if not show zero' so then Qty won in the pivot table is summing the '1s'. The Qty Quoted is a more standard count field in the pivot table based on the Month Quoted (which is the Month shown) simply counting how many times a quote appears in that Month.
I have tried several ways to add the calculated field, but it keeps giving me the same information (as below).
I appreciate any help.
Thanks
smk511
<tbody>
</tbody>
I apologize if this becomes hard to follow, I am very new to posting in things like this so very well may not give all of the right info or have all of the correct terminology. I use your forum regularly for help as I am learning Excel, but couldn't find a solution to this.
I am trying to create a % within a pivot table that will be based on 2 other fields that are already in the pivot table. One of the columns is a count and the other is a sum, here is what the pivot table looks like. I need the % Closed to be Qty won divided by Qty Quoted so we can show a close rate.
Qty won is a 'sum' field where I have an if formula in the source data saying basically 'if a quote was won, show 1, if not show zero' so then Qty won in the pivot table is summing the '1s'. The Qty Quoted is a more standard count field in the pivot table based on the Month Quoted (which is the Month shown) simply counting how many times a quote appears in that Month.
I have tried several ways to add the calculated field, but it keeps giving me the same information (as below).
I appreciate any help.
Thanks
smk511
Row Labels | Qty Quoted | Qty won | Revenue Quoted | Revenue Won | Count of % closed |
January | 185 | 31 | $571,498 | $182,788 | #DIV/0! |
February | 166 | 16 | $427,046 | $107,277 | #DIV/0! |
March | 182 | 33 | $915,335 | $172,468 | #DIV/0! |
April | 209 | 29 | $550,588 | $88,816 | #DIV/0! |
May | 193 | 29 | $682,574 | $190,881 | #DIV/0! |
June | 164 | 19 | $684,195 | $154,668 | #DIV/0! |
July | 211 | 26 | $752,461 | $265,622 | #DIV/0! |
August | 265 | 26 | $1,078,066 | $168,142 | #DIV/0! |
September | 214 | 23 | $1,006,264 | $183,330 | #DIV/0! |
October | 202 | 24 | $996,062 | $101,103 | #DIV/0! |
November | 196 | 19 | $985,644 | $151,458 | #DIV/0! |
December | 171 | 26 | $566,552 | $0 | #DIV/0! |
Grand Total | 2358 | 301 | $9,216,286 | $1,766,552 | #DIV/0! |
<tbody>
</tbody>