Calculate Avg Cost per conversion formula with 3 conditions

**alfykunable** · 07-02-2012, 04:05 AM

Hi All,

I am trying to run a report and there are 3 dimensions i need to run it on I have a product that has more than 3 campaigns that run in different locales and i have their performance on a weekly basis, i can get a Cost per conversion for each campaign by dividing the cost of the campaign by number of conversions, but what I need is,

(total cost of product for locale for week1/total conversions for product for locale for week 1)

Is there any excel formula/vba code that can give me this value?

I've attached a samplesheet and the column in which I'd live the value. I need this column for running other pivots.

Thanks

Alfred

**Fotis1991** · 07-02-2012, 04:12 AM

Hi

Do you mean?

=AVERAGE(IF(C2:C465=C2,A2:A465=A2,E2:E465))

Array formula(Control+Shift+Enter)

**alfykunable** · 07-02-2012, 04:50 AM

Hi Fotis,

Thanks for the formula, but sadly its not calculating the correct thing, its taking

(total cost for Product SOA in week 1/total conversions for SOA in week1)

what i need is

(total cost for Product SOA in Locale UK in week 1/total conversions for SOA in Locale UK in week1)

Thanks

Alfred

**Fotis1991** · 07-02-2012, 05:01 AM

Hi again.

As you are in Excel 2010, give a try to AVERAGEIFS.

=AVERAGEIFS(E2:E465,A2:A465,A2,C2:C465,C2,D2:D465,D2)

This does the average of the amount, if Product is SOA, Location is UK and Week is 1..