help on calculated field.

[SG]

hi Experts,


I'm trying to replace a manual mapping by using calculated field in pivot.however, it's not giving me the correct result.can anyone please look in to this & give me a more better solution.please help me.I have uploaded a sample file at below location:-


http://www.2shared.com/file/DO_Ocfeq/atm.html

 

[Faseeh]

Hi SG,


I tried to manually workout the formula and then use PT. I have deleted one row that was giving Div Error. Can you confirm the result??


http://img.chandoo.org/faseeh/atm_cross%20question.xlsx


Regards,

 

[SG]

Hi Faseeh,


I wanted to make calculated filed in pivot.however,i firstly tried out your way.I want to reduce sheet size as manually mapping the formula makes sheet heavy because data is in thousands.anyways.thanks for looking in to this.

 

[Faseeh]

Hi SG,


Thanks for feedback If you can confirm the results, the table can be tried upon once again why the formula is not giving desired results.


Faseeh

 

[SG]

hi Faseeh,


Results are not correct.Their score can't be more than 2.

Please experts help me on this.


Regards,

SG

 

[Faseeh]

Hi SG,


I was asking you to check the manual calculation because it is easier to pick fault in detailed view rather then in compact formula view.


I think the problem is in your formula not in the way it is entered in Pivot Table. If you look at the attached file:


http://img.chandoo.org/faseeh/PivotTableCustomFormulaWorking.xlsx


The data in the reworked table is still showing zeros that when pivot will sum up will show zero.

[pre]

WeekNo	RepTo	IntResRev IntErr Teams	usl	lsl	int	Scr
24	a	72	    1  	  na	99%	91%	1.39%	0
24	a	46	    0	  na	99%	91%	0.00%	0
24	a	27	    0	  na	99%	91%	0.00%	0[/pre]

..When we evaluate your formula it goes like this:


[code]=IF(int>usl,2,IF(int<lsl,0,((2/(usl-lsl)*(int-lsl)))))

Working inside out, IF(int<Isl,0,FalseCondition)[/code]


Since almost all of your Int are less then respective Isl, this results in execution of the inside IF() and the entire column is filled with zeros, and when you use PTable, It gives you zeros.


So problem is not in how PT executes it but it is in your formula. That is my humble opinion.


Regards,

Faseeh

 

[SG]

hi Faseeh,


Problem is not in the formula.for int% your formula is 1*(int errors/int resp reviewed) whereas the formula is "1-(int errors/int resp reviewed)".I have specified this formula in first sheet.Manually,i'm doing this from a year & results are correct.i just wanted to make it more convenient & replace manual mapping.


Regards

SG