Comparative calculations in Pivot

[wallywalter]

I have a file of purchases of several items in a year. Items are purchased from single vendor or multiple vendor. Rates vary within many purchases, including that of single vendor. I have constructed a pivot table based on item code. Now want to analyse the variation of every single / line rate against average rate of total purchases and minimum rate of the purchases. To simplify the understanding I have attached a sample file.

E.g. referring to the attached file

In case of item ABC , the highest rate is 4, while lowest is 2 and average rate of those purchases is 3. I want to insert additional columns in the pivot to indicate variation of every line item rate against lowest rate and average rate. Hence for first line item, that will comparison of rate 2 against lowest rate 2 itself. Next column will have comparison of line rate 2 against average rate 3.

So on...

[:) Sixthsense :)]

Yes, it can be done in pivot and its better to use a helper column in your pivot source data itself and perform the calculation and project it in pivot.

Just add the expected output in your attachment so that it will be helpful in giving solution.

[wallywalter]

Yes, it can be done in pivot and its better to use a helper column in your pivot source data itself and perform the calculation and project it in pivot.

Just add the expected output in your attachment so that it will be helpful in giving solution.

Thanks ! But could you please show me with an example in the attachment ? Somehow I could'nt figure out to do so.

[:) Sixthsense :)]

Thanks ! But could you please show me with an example in the attachment ? Somehow I could'nt figure out to do so.

Just you fill the expected output in Column-F of Sheet4 (Pivot Sheet), so based on that the formula solution or pivot solution will be provided to you.