Formulas in Pivot based on average

[Lillian Eik]

Hi

I cannot get Pivot to calculate with amount in column, it sumarises
from basesheet.

I have 3 columns in base sheet:
Costcenters (multiple times), cost element (only one pr. cost center),
cost

Then I use 2 vlookup's on costcenter to get an A&B allocation % (Total
100%) pr costcenter. This value I included in Pivot, but Pivot summed
up the number of times. F.ex on 1 costcenter I had allocation 75% Then
Pivot sumed up number of times 75% were looked up on that costcenter as
costcenter appeard in many rows as linked to different costselements.

I managed to use a max/average formula so now it actually shows right
values in Pivot, but when I want to use THAT value to calculate with
cost column in Pivot. But it stills calculate with the sum from
original sheet and not the value in The column in Pivot.

Cost Center; A; B; Costs; Sum of A
(formula (costs*A);
FA30202; 0,75; 0,25; 3 656 793; 137 129 724;

I want the answer 2 742 594. But as you see the answer Pivot gives me
is 137 129 724

Here Excel takes number of times 0,75 is in base sheet (50
costelements)=37,5

Somebody having a suggestion for this. Very greatfull if a solution
exists
Thanks

 

[Debra Dalgleish]

In the source data, you could modify the formula so it only looks up the
value for the first instance of each cost centre. For example:

=IF(COUNTIF(A$1:A2,A2)=1,VLOOKUP(A2,LookupTable,2,0),0)