The INDEX-MATCH formula is a superior way to perform LOOKUPS in Excel.Īt this point you should have all of your comparison metrics pulled into your “current” worksheet. We will be using the “current” worksheet as our main data source for our pivot table and charts. If done correctly, all we will have to do to update our dashboard is append new data to our “current” worksheet.īecause we are going to be aggregating one calculated metric (AvgPos) we need to create a weighted average position. We do this by adding the following formulas in new columns to the right of our data set: Modify the formula above for each comparison metric (Prv_Impr, Prv_Clicks, Prv_cost, Prv_AvgPos, & Prv_Conv). Using the IF(ISNA formula gets rid of “N/A” errors when there is no match. We need “0’s” not “N/A’s” for our pivot table to work properly. ![]() Next we will use the INDEX-MATCH formula(lookup) in combination with the IF(ISNA formulato pull over our previous performance data to our “Data” worksheet. Use the following formula for your “Previous” data set: This should result in something like: 40544Campaign or (date)(campaign name) The next thing we need to do is tie the previous data back to our current data. To do this we will need to create a unique identifier or primary key. In the right-most column in your “Data” worksheet, add the following formula (this assumes you have formatted your data as a table in Excel): Pull the previous date report as described above and put the report in a worksheet named “Previous.” Pull a Year-To-Date report from the Dimensions tab (might make sense to schedule this report) using the columns shown above and put the report in a worksheet named “Data.”Ģ. I offset my previous data range to match the same day of the week, not just the same date, ( – ) to get a more accurate comparison. Eventually I plan on updating my current report data regularly, so I decided to pull all of last year’s data ( – ). This way I will only have to update one report. ![]() Because we will need to be able to do date range comparisons, you will need to pull two reports: a current and a previous performance report. In my example I pulled YTD data ( – ).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |