Calculated Fields and IF function

[Tarver]

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?

 

[Fazza]

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

 

[Tarver]

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.

 

[Fazza]

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.