I'm working on a movie box office revenue spreadsheet. A pivot table was created for the movie data to make calculations of sum, average and count of revenue generated. Now I want to use a calculated field to verify my average.
I copied my original pivot table into another row on the same sheet and this copied table has been filtered with a new condition. Then I created a customized column called calculated field using the values menu and entered this formula below
=SUM(box office revenue ($))/COUNT(Box Office Revenue ($))
In other to get the average revenue of the new condition I applied. But I received a formula parse error message after writing this formula.
Initial Input markdown
| Release Date - Year | SUM of Box Office Revenue ($) | AVERAGE of Box Office Revenue ($) | COUNT of Box Office Revenue ($) | |
|---|---|---|---|---|
| 2012 | 18,078,040,000.00 | 170,547,547.17 | 106 | |
| 2013 | 13,672,800,000.00 | 160,856,470.59 | 85 | |
| 2014 | 20,013,420,000.00 | 168,180,000.00 | 119 | |
| 2015 | 13,521,310,000.00 | 109,042,822.58 | 124 | |
| 2016 | 11,921,900,000.00 | 161,106,756.76 | 74 | |
| Grand Total | 77,207,470,000.00 | 151,983,208.66 | 508 | |
| Release Date - Year | SUM <$10M | AVERAGE <$10M | COUNT <$10M | Calculated Field 1 |
| 2012 | 67,940,000.00 | 6,794,000.00 | 10 | 6,794,000.00 |
| 2013 | 26,400,000.00 | 3,300,000.00 | 8 | 3,300,000.00 |
| 2014 | 46,920,000.00 | 4,265,454.55 | 11 | 4,265,454.55 |
| 2015 | 93,040,000.00 | 4,652,000.00 | 20 | 4,652,000.00 |
| 2016 | 41,100,000.00 | 5,137,500.00 | 8 | 5,137,500.00 |
| Grand Total | 275,400,000.00 | 4,831,578.95 | 57 | 4,831,578.95 |
Expected output is the same value in the AVERAGE<10M field. I want to use the calculated field to verify my average because it was copied from another pivot table on the same sheet before I filtered it with a condition.
Your formula produces an error because the SUM part uses lowercase letters (b, o, r). Both SUM and COUNT in the formula should use uppercase letters ( B, O, and R) matching the pivot table, and the formula should include a single quote.
You may try this formula:
=SUM('Box Office Revenue ($)') / COUNT('Box Office Revenue ($)')