Calculated field error

egenna

New Member
Joined
Feb 18, 2021
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
Trying to create a calculated field and I am getting the error message "We found a problem with this formula. Try clicking on the insert function of the formula tab to fix it...."
My field on the pivot table is called type. I am filtering so it only shows the financial period, Revenue and Cost of Sales on the columns. I want a calculated field that shows ='Sales Revenue' - 'Cost of Sales' / 'Sale Revenue'. Seems like it should be simple but it won't let me create it. What am I doing wrong?
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,126
Welcome

Is your PivotTable based on OLAP source data?

Can you provide a link to a sample workbook that has the issue?
 

egenna

New Member
Joined
Feb 18, 2021
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
I am not sure was OLAP source data is. It is an excel spreadsheet. I can't seem to get xl2BB installed so here is an image of my simple pivot table. There are only three fields. Period, Value and Type. The type is my columns: Cost of Sales and Sales Revenue.
Thank you.
 

Attachments

  • Capture spreadsheet.PNG
    Capture spreadsheet.PNG
    30.8 KB · Views: 5

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,126
See if the following example helps; if not, please post sample source data for the pivot table and its structure as shown below, so that I can recreate it.

Cópia de budget pivot table2.xlsm
YZAAABACADAEAF
4Row LabelsCost of Sales1Sales RevenueCalculatedCalculated Field
52019/01805431767650,544349843Solve OrderFieldFormula
62019/02816541756520,5351376591Field1= (Revenue -'Cost of Sales' )/Revenue
72019/03827651745390,525807986
82019/04838761734260,516358562
92019/05849871723130,506787068
102019/06860981712000,497091121
01-Feb-16


ptable.PNG
 

egenna

New Member
Joined
Feb 18, 2021
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
I can't seem to create the field. I get the error message "There is a problem with this formula. Not trying to type a formula? When the first character is an equal or minus sign, excel thinks its a formula;.... " However,. I am trying to create a formula.


My source data is a very simple excel spreadsheet with three columns, period, home value and type.
Similar to the table below.

2019/101-947.3Cost of Sales
2019/101-547.76Cost of Sales
2019/101-12.29Cost of Sales
2019/101-3.19Cost of Sales
2019/1013.19Cost of Sales
2019/101-13.84Cost of Sales
2019/10113.84Cost of Sales
2020/001-98655.62Operating Expenses
2020/001-204432.24Operating Expenses
2020/001-214380.82Operating Expenses
2020/001812168.65Operating Expenses
2020/001-27388.07Operating Expenses
2020/001-1904877.3Operating Expenses
2020/001-611808.42Operating Expenses
2020/00155629.24Operating Expenses
2020/001-39700.71Operating Expenses
2020/001267685.98Operating Expenses
2020/0011349905.81Operating Expenses
2020/001-22958.64Operating Expenses
2020/001-176008.06Operating Expenses
2020/001-184104.64Operating Expenses
2020/001-92133.09Operating Expenses
2020/0013306.25Other Income/Expense
2020/0013306.25Other Income/Expense
2020/0013306.25Other Income/Expense
2020/0012066.81Other Income/Expense
2020/001757.21Other Income/Expense
2020/0011092.36Other Income/Expense
2020/001-4360.01Other Income/Expense
2020/001-478.75Sales Revenue
2020/001-478.75Sales Revenue
2020/001-478.75Sales Revenue
2020/001-478.75Sales Revenue
2020/001-478.75Sales Revenue
2020/001-478.75Sales Revenue
2020/001-478.75Sales Revenue
2020/001-478.75Sales Revenue
2020/001-478.75Sales Revenue
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,126
  • Take a look at the following page to see if you are missing something.
  • I need you to inform the pivot table layout so I can produce an identical one, as below.
ptable.PNG


 

Watch MrExcel Video

Forum statistics

Threads
1,127,422
Messages
5,624,697
Members
416,042
Latest member
Oden

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top