Calculating Variance Based on Matching Multiple Elements

[bgauthier]

Hi -

I am trying to count month-to-month variance for keywords in some campaigns I am running. One issue I'm running into is how to calculate when some keywords are the same in multiple campaigns. For example:

Month Campaign Keyword Purchased
Apr-13 Produce - US Apples 3
Apr-13 Produce - CA Apples 1
Apr-13 Produce - US Pears 2
Apr-13 Produce - CA Pears 0

Mar-13 Produce - US Apples 1
Mar-13 Produce - CA Apples 1
Mar-13 Produce - US Pears 1
Mar-13 Produce - CA Pears 1

So what I need it to be able to do is show the change from the previous month (i.e. Produce - US: Apples would be +2 and Produce - CA: Pears would be -1)


I have the following formula that seems to work when it comes to matching keywords, but it cannot differentiate by campaign. We used ranges to separate current month keywords from previous month keyword data.

=IF((current_kywd_conv-last_mo_kywd_data)=0,"-",((current_kywd_conv-last_mo_kywd_data)))

Thanks for your help!

[Excel-course.com]

Hi,

Two questions:

(1) How did you get current_kywd_conv-last_mo_kywd_data?
(2) Is the data in 4 columns (Month, Campaign, Keyword, Purchased) or 1?

[bgauthier]

Excel Example.xlsx

Hi Excel-course.com -

Here is an example that may help. What I'm trying to do is calculate the change column to reflect month/month variance. The problem is that the formula cannot differentiate between campaigns when both have the same keyword. What we have done is copy the previous months keywords down in a section that is typically hidden, see rows 58-188. We then compare current month data against this to come up with the variance in conversions.

[Excel-course.com]

Hi bgauthier,

Thank you for sharing that spreadsheet. That helped. I understand the problem you are having.

Can you provide the code for how you get:
- current_kywd
- last_mo_kywd and
- current_kywd_conv-last_mo_kywd_data

I understand what you are doing in general, but to help improve the code to take into account the campaign, I think I need more detail.

Thanks.

[bgauthier]

Hi Excel-course.com -

Those are just named ranges. That way if we added or removed columns we didn't have to worry about disrupting our formulas. So current_kywd_conv is range (C8:C52) then we subtract last_mo_kywd_data (range F9:F52). current_kywd is range B9:B52 and last_mo_kywd range should be B59:B188. We manually download this data from our system each month and use paste special: values to input it into the report. So the data could change each month. Rows 8:52 were are only reporting on the Top performing for the current month. Usually we do Top 50, but I pared it down for this example. Last month's keyword data will usually reflect all keywords because it was easier for calculation.

Thanks for taking a look at this for me, it is much appreciated!

[Excel-course.com]

Hi bgauthier,

Thank you for clarifying on your named ranges. You're welcome, I'm happy to help you out.

For cell F9, go ahead and try this formula:
=IFERROR(INDEX(C$60:C$188,MATCH(1,(A9=A$60:A$188)*(B9=B$60:B$188),0)),0)
and Press simultaneously Ctrl-Shift-Enter to enter this formula, so Excel places {} around the whole formula like:
{=IFERROR(INDEX(C$60:C$188,MATCH(1,(A9=A$60:A$188)*(B9=B$60:B$188),0)),0)}

This should give you an idea of how to do a MATCH on two criteria.

You can then drag this formula down from Cell F9 to F52 to get all last month's conversions for each campaign and keyword.

Please let me know if that works for you!

[bgauthier]

Hi Excel-course.com -

Thank you so much that looks like it worked!

[Excel-course.com]

Hi bgauthier,

Good to hear! It was a pleasure helping you. Please feel free to let me know if you have any additional questions in the future.