Multiple IF statements, combined with Index, Small and Row functions

**wallyjuk** · 01-15-2016, 09:33 AM

Hi all,

I'm working on creating an Excel dashboard that shows which online promotions have driven the most revenue, transactions, average order value etc.

The data will be selected from two combo boxes, one for the week of the year, one for the calendar year.

The formula below worked fine before I began to add in 2016 data - now it returns duplicate values if the same promo code was used in both 2015 and 2016.

Cell B4 is the week, and Column B in the raw data tab is the list of weeks to which the other data pertains.

{=IF(ISERROR(INDEX('BRAND Raw Data'!$B$1:$E$10000,SMALL(IF('BRAND Raw Data'!$B$2:$B$10000=$B$4,ROW('BRAND Raw Data'!$B$2:$B$10000)),ROW(1:1)),3)),"",INDEX('BRAND Raw Data'!$B$1:$E$10000,SMALL(IF('BRAND Raw Data'!$B$2:$B$10000=$B$4,ROW('BRAND Raw Data'!$B$2:$B$10000)),ROW(1:1)),3))}

I have tried to add in IF('BRAND Raw Data'!$C$2:$C$10000=$C$4 with Column C being the list of years and C4 being the year selected, but I am going wrong somewhere!

The ROW(1:1) becomes (2:2), (3:3) and so on, to show all the promo codes that were used in a given week.

I'd really appreciate any help and I hope my explanation is clear.

Many thanks,

Matt

**mcmahobt** · 01-15-2016, 09:40 AM

Much easier if you could provide a sample workbook with how your data is setup. Also a word of caution if you're using the WEEKNUM() function in Excel, especially towards the start of a new year (think of a week that is split, so week 52 may end on a Wednesday and week 1 of the new years starts on a Thursday, thus potentially skewing data).

**wallyjuk** · 01-15-2016, 10:02 AM

Hello, thanks for the quick reply.

I've quickly deleted all but one part of the dashboard and anonymised the data so I can share - https://drive.google.com/file/d/0B3d...ew?usp=sharing

My company has its own retail calendar, so the 'weeks' aren't always 'proper' weeks if you see what I mean! The actual dates of the transactions are index-matched against the retail calendar weeks on the reference sheet.

In the document linked above, "No code used" appears twice because this is valid for Week 01 in 2015 and 2016, and I can't figure out what to change in the formula to make the second combo box work to select the year.

Thank you for your help - any guidance appreciated.

Matt

**mcmahobt** · 01-15-2016, 10:29 AM

I see that column D in the BRAND Raw Data sheet contains (seemingly) unique values (e.g. GREAT promo code becomes GREAT_-3.6, GREAT_-3.3, etc.). Do you want to lump all the "GREAT" promo codes that occur in a given week? Or pull each individual promo code?

**wallyjuk** · 01-15-2016, 10:31 AM

Hello, they should be grouped as one "GREAT" promo so I've cleaned them in column C. Attributes like _-3.3 were added when the data was exported from Google Analytics.

Thanks

Matt

**mcmahobt** · 01-15-2016, 12:29 PM

Taking a further look at your data shows that this is actually one of the most often requested Excel tutorials on the web, and the short answer is there's no real succinct method to go about achieving it. If you take a look at the attached workbook, you'll notice I added a helper coulumn to the BRAND Raw Data tab, as well as added a data table to pull the filtered data on the Reference Sheet tab.

From there, you can create your ranked list. The only hiccup comes in when you try and rank duplicate values (Promo codes that only occur once a period), but if you are okay with those being shown in any order, then this should work for you.

Columns F:I have been left blank for you to fill in, but if you require more assistance just let us know.