Calculations within Pivot Tables

Hello all, new to the forum and I've got a question that I don't have the time to research anymore.

Basic question:
Is there a way to perform calculations, in this case basic subtraction, within pivot tables?

Background:
My company currently uses Excel as a Resource Management tool. We're not entirely concerned with the granularity offered by Project and other project or time management tools. We basically need to say that Employee A is on Project 1 from Start Date to End Date. We do that by inputting a decimal value (corresponding to percent of Employee A' time) between 0 and 1 into a column that corresponds with an individual week in the year. With this method we create two different reports using pivot tables. The Individual Project breakdown shows all employees on all projects and the percentages associated with each. The Headcount only shows an employee as '1' for whichever Project they are being counted for in the monthly headcount numbers. If an employee is 50/50 on two projects, he will only be shown as a '1' on whichever one is his Headcount for the month. Reflecting an employee on two projects requires a line for each project he's currently on.

I've attached an example, and you'll quickly see that this is a really sucky way of doing things. My best excuse is that it was handed down from on high and I've made it work as best I can.

My current problem is doing calculations within Pivot Tables (if possible with the current setup). For various reasons we have a 'Multiple' Project designation that is basically a sum of all the employee's current time. The idea is that it will show availability and other information, but for other reasons it has to stay the sum of all the employee's current utilization. I want to be able to capture that line in a Pivot table, subtract it from 1, and show availability. IE, if Employee A is only 75% or .75 utilized for a week, I want a Pivot Table or some sort of automatic display that shows him as 25% or .25 available for that week.


Also, feel free to point out glaring problems in the design of this spreadsheet and any other issues or questions you may have. At this point I'm so far down the rabbit hole that I'm not sure it I'm looking at daylight or an oncoming train... Once I get a few more details with this spreadsheet hammered out, my next task is to migrate it all to a robust Access database.... Thanks for what help may come!

use SQL. I perform the SQL query in Excel so the syntax may look a little big funky, but you get the point

Please Login or Register  to view this content.

I'm a little confused on what you're telling me to do. Are you saying I should use a SQL database for the entire job, or just make that query in Excel and it will work in a pivot table?

Any way you could provide an example with the example data I had above? Do you have any links to some reading that would give me a little more information?

Edit 12:10EST: Another question I have is filtering within the pivot tables with the current setup. Using the sample data above, how could I change the first pivot table, Individual Project Breakdown, to only show numbers less than 1, or only show numbers that equal 1? It may be my misunderstanding of pivot tables, but I haven't figured out how to do it with the way we have it now.