Calculated Fields and IF function

Tarver

Board Regular
Joined
Nov 15, 2012
Messages
109
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I have a PivotTable with just two fields. I'd like to create a calculated field to manipulate that data.

IF the DESCRIPTION field is "Contribution" I want to multiply by -1.
IF the DESCRIPTION field is "Withdrawl" I want to multiply by -1.
IF the DESCRIPTION field is anything else, I want to leave the value alone.

My data is in an AMOUNT field.

I'm trying to create a Calculated Field formula to do this calculation, but even the first part gets hosed and returns a value of 0.

My formula:
=IF(DESCRIPTION="Contribution",AMOUNT*-1,IF(DESCRIPTION="Withdrawl",AMOUNT*-1,AMOUNT))

By the "normal" rules of Excel, this formula works (assuming I have named ranges corresponding to DESCRIPTION and AMOUNT.) As a calculated field in a pivot table however, it's chaos!

Is there something wrong with the formula above that won't work in a calculated field? What should I be doing instead?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Create the pivot table with SQL including : IIF(OR(DESCRIPTION = 'Contribution', DESCRIPTION = 'Withdrawl', -1, 1) * AMOUNT AS [Name Of New Field]

Note, withdrawal spelling per your post - 'Withdrawl'

For explanation, I'll assume the source data table has a simple (non-dynamic) defined name "tblData". This is not the only way.
Give the data table a defined name (CTRL-F3). Save the file (CTRL-S). Open a new file (CTRL-N), ALT-D-P to start the pivot table wizard. Choose external data source at the first step (ALT-E), next, get data, browse, excel files, etc, etc. At the last step take the option to edit in MS Query. Then via the 'SQL' button edit the text to add the new field. it will be something like

SELECT DESCRIPTION, AMOUNT FROM tblData

And this should suffice, btw. You can edit to the above and test via the OK button & seeing the dataset.

Edit it to become SELECT DESCRIPTION, AMOUNT, IIF(OR(DESCRIPTION = 'Contribution', DESCRIPTION = 'Withdrawl', -1, 1) * AMOUNT AS [Name Of New Field] FROM tblData

OK to enter. OK to acknowledge any message you may get about not being able to graphically represent it. See the results dataset. 'Open door' icon to exit MS Query & complete the pivot table. If you want, move the resultant worksheet into the source data file.

cheers
 
Upvote 0
Thanks for taking the time to try to help.

I'm sure this is a great answer, but I just don't have the ability to comprehend it. I've looked through Excel to find a way to enter a SQL query and have spent an hour or so online trying to follow various tutorials to make this work. I'm just not skilled enough to do it.

I appreciate your efforts.
 
Upvote 0
We can try further if you're interested - explain which step is the roadblock along the route of
I'll assume the source data table has a simple (non-dynamic) defined name "tblData". This is not the only way.
Give the data table a defined name (CTRL-F3). Save the file (CTRL-S). Open a new file (CTRL-N), ALT-D-P to start the pivot table wizard. Choose external data source at the first step (ALT-E), next, get data, browse, excel files, etc, etc. At the last step take the option to edit in MS Query. Then via the 'SQL' button edit the text to add the new field. it will be something like

SELECT DESCRIPTION, AMOUNT FROM tblData

And this should suffice, btw. You can edit to the above and test via the OK button & seeing the dataset.

Edit it to become SELECT DESCRIPTION, AMOUNT, IIF(OR(DESCRIPTION = 'Contribution', DESCRIPTION = 'Withdrawl', -1, 1) * AMOUNT AS [Name Of New Field] FROM tblData

OK to enter. OK to acknowledge any message you may get about not being able to graphically represent it. See the results dataset. 'Open door' icon to exit MS Query & complete the pivot table. If you want, move the resultant worksheet into the source data file.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

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
Back
Top