Note: data ends on row 18, so the calculation is as follows: $1,006.75 / 739 = $1.36
Fields
The source data contains three fields, Product, Quantity, and Sales. A fourth field called “Unit Price” is a calculated field.
The calculated field was created by selecting “Insert Calculated Field” in the “Fields, Items, and Sets” menu on the ribbon:
The calculated field is named “Unit Price” and defined with the formula “=Sales/Quantity” as seen below:
Note: Field names with spaces must be wrapped in single quotes (’). Excel will add these automatically when you click the Insert Field button, or double-click a field in the list. The Unit Price field is renamed “Unit Price " (note the extra space) after it has been added to the Values area:
The extra space is required because Excel won’t allow you to use exactly the same field name that appears in the data in a pivot table.
Steps
Author
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
