Subtract one pivot column from another, within the pivot table

[MikeGreen1972]

Good Afternoon,

I asked this question about a week ago and never got any responses, so I thought I'd ask it one last time. Any help is greatly appreciated, as this one is driving me nuts. In short, I have a multi column pivot table calculating student throughput for classes we teach throughout the FY. Within the pivot I have an allocation column (number of seats) and a fill column (number of students). I need to figure out how to subtract one from the other to give me the total number of vacancies, per class. I can do this outside the table of course, with a simple subtraction formula, but really want this to be a part of the pivot table so as things move/adjust, the rows will stay synched. I've tried using a calculated field as well, but it gives me a value of zero every time. Anyone have any ideas?

 

[ghrain22]

It sounds like a calculated field should work based on your description.

What does your source data look like?

In the source data for the pivot, are the objects your trying to subtract in the same column or seperate columns (ie are they in the same field)?

 

[MikeGreen1972]

They are all in the same Table/Range from multiple columns. For example, my data sheet has a column called position and another column called Student Name (as well as many other columns). In short, my pivot lists all the available positions (slots for a class) and does a 'count of' next to them. I have another 'count of' from the Student Name field that counts all of the students against the available slots. what I need in a new column is the count values subtracted from one another.

For example:

ROW Labels count of position count of student Name

- San Antonio Medics 56 40
- 4N051s 12 9
- 4N071s 12 11
- 4N151s 12 7
- 4N171s 12 5
- 4H051s 8 8

trying to cut and paste this in here as a table, but it isn't working too well. my apologies.

 

[SpillerBD]

The calculated fields should work, but there can be some obstacles depending on data organization and formatting.
I don't use them often, but seems like I've had trouble getting desired results sometimes.
Can you share a sample of your WorkBook? (PM?)

 

[SpillerBD]

Count of.... fails as a source.
The calculated fields goes back to the raw data which is not numeric, which therefore calculates as zero...
I think your desired result is achievable. I would probably try and pivot a pivot table. The first generates numeric values that can then be calculated on...
The more specific an example you can provide, the more specific proposed solutions can be.

 

[MikeGreen1972]

Tried to PM you. How do I get a copy of the spreadsheet to you? I can't find a way to attach it. thx

 

[SpillerBD]

Been sick...
You could add a field to your source data with a value of 1 in it. Call it Count or similar. Then your Pivot Table will have an actual numeric to calculate on from your source data, which will then allow you to create a calculated field.