PDA

View Full Version : Solved: Formula results are different then Pivot Table



JimS
01-25-2011, 07:38 AM
Below are 2 Array Formulas that do not produce the same results from the data when using a Pivot Table.

They are close but 2 records are being counted/sum’d incorrectly.

I have attached a sample file.


The formulas use the following Range Names:

OppN = Range Name for the Operation Number column on the Data worksheet
Split = Range Name for the Cost column on the Data worksheet
District = Range Name for the District Name column on the Data worksheet
Cat = Range Name for the Category column on the Data worksheet


Formula #1:
This formula should count the unique Operation Numbers (OppN) if the Cost (Split) of the Operation Number is between 50000 – 749999 and if the District is F1, F2, Metro, HQ & South and the Category equals H1 or S1.

{=SUM(--(FREQUENCY(IF((SUMIF(OppN,OppN,Split)>=500000)*(SUMIF(OppN,OppN,Split)<=750000)*((District="F1")+(District="F2")+(District="Metro")+(District="HQ")+(District="South"))*((Cat="H1")+(Cat="S1")),MATCH(OppN,OppN,0)),ROW(INDIRECT("1:"&ROWS(OppN))))>0))}


Formula #2:
This formula should Sum the Cost column (Split) for the unique Operation Numbers (OppN) if the cost of the Operation Number is between 50000 – 749999 and if the District is F1, F2, Metro, HQ & South and the Category equals H1 or S1.


{=SUM(IF((SUMIF(OppN,OppN,Split)>=500000)*(SUMIF(OppN,OppN,Split)<=750000)*((District="F1")+(District="F2")+(District="Metro")+(District="HQ")+(District="South"))*((Cat="H1")+(Cat="S1")),Split))}

Any idea why these formulas are not functioning correctly?

Thanks...

JimS

JimS
01-25-2011, 10:16 AM
It appears that the data in Row 34 and 190 on the Data worksheet is what is not getting calculated correctly by the formulas.

Now just to figure out why...

JimS
01-25-2011, 02:04 PM
I need to re-valuate how I want the results.

Can this Post be removed?