"Complex" formulas in pivottable?

[Bpuad]

Hi,

I have a problem that I can't seem to figure out.

I have a list with job grades and am trying to calculate difference in sallary for men and women. As it is today I have calculated this outside the Pivot table but would like to have the formula inside the pivot. As my skills in Pivot is very limited I've tried to use "Calculated Field" but with no success. Se picture below how I have structred it today, the yellw fields are the calculations I've done outside the pivot table (formals are shown below each of the 2 cells).

Any ideas how I can include this within the pivot table as formuals? Or any other ideas how to solve this?

/Per

 

[FoxSeaLady]

I have been able to create a calculated field using Excel 2016.
Using the data Col c to G created the basic pivot table then clicking "Fields, Items & Sets created the required field ignoring the if conditions for both formula.
You can then filter the pivot table to not display where employee value = 0.

Does this help?

 

[Obied70]

I've also been trying to get complex formulas into a pivot recently. If you do a calculated field, you're limited to using a formula that only contains items from the same field. You also cannot use cell references (as far as I have seen) in a calculated field. If you can get your formula into your data set, it will become part of your pivot table. Aside from that, I'm still looking for a solution myself.

 

[FoxSeaLady]

I set up a data set with column headings Row Labels, Female Employees (Number), Female average Salary, Male Employees (Number), Male average Salary.

Then created a pivot table using the data supplied. I added 2 more rows of data.

Then Pivot table analyse/Fields, Items,& Sets/Calculated Field.

Entered Field Name Gap in the window box with the formula 'Female Average Salary' - 'Male average salary'
Next completed the same for Statistical GAP where formula GAP*Female employees.

After my initial reply tried to add a standard If ('Female Employees' <1,0,'Female average Salary' - 'Male Average Salary')

This returned 0 where females were 0. While this still displayed the row it deletes from the totalling.

Cheers

 

[Bpuad]

Sorry I forgott to mention that shown above is the pivot tabel, the table was derived from a individual list with employees.

FoxSeaLady, I will try your version.

Any other sugestions.

 

[Bpuad]

Yes same here, it's frustrating!

 

[Bpuad]

Sorry can't get it to work as the pivot is based on an individual list. I also tried to create an pivot table out of the pivot table I pasted above but to no success, Excel didn't like it.

Any other sugestions how this could be solved, I really need a solution to this.

/Per

 

[Bpuad]

Ahhhhh I solved it

I created a pivot table from the pivot table I pasted in my first post by pressing the key combination (Alt+D+P) in a new sheet. In the list of data source options choose "Microsoft Excel list of database" and then just followed the instructions .

Doing it this way I can use the formulas (marked in yellow in my first post) and include them in my new pivot and all is good

/per