You Need to Include User Experience
This week I had a request for help in designing a report for submission to owners. Previously, the managers have been pulling a bunch of reports and trying to manually produce the report, but on Thursday I got a cry for help. Understandably, they just got tired of doing it manually. And this request wasn't just solve the technical problem, although it was couched in those terms, but it also needed to include the user experience.
The request seemed simple enough but there was one piece of the report that required a rethinking of the approach. Previously, we had a static list of ad sources on the report that was reported on, week after week. But this was a list for company roll up purposes and not for a specific client; therefore, the regional manager wanted the flexibility of changing the ad source list to fit their needs. I thought, “Okay, they need their own static list to be reported rather than using our list”. But then the regional manager mentioned they didn’t want to see all of the zeros which led me to another thought, “They really want a changing ad list each week, based upon what happened during the week.” And the problem became immeasurably harder… interesting but harder.
And it turns out that an array formula does the trick.
I could have the manager manually list out on the report the ads used for the week but that would require spelling exactly what was listed in the data downloads and I didn’t want to depend on them spelling correctly. I needed to get them out of spelling the ad sources; instead, the data from the downloads needed to flow into the report. From week to week, the type and number of ad sources could change so I needed a formula that could generate this list flexibly.
I found the basic formula, to be altered to fit my needs, from this website and it looks like:
{=INDEX(list,MATCH(0,COUNTIF(uniques,list),0))}
The “list” is the list of items that we want to pull a list of unique items. In my case, it is a particular column in a data download that lists out a certain feature. The “uniques” is actually your list of unique items that you are developing from the original list. The thing about this “uniques” is that it starts from the cell just above the cell where you start your list of unique items. For instance, say that your list of unique items begins in cell D3. The “uniques” would be shown as $D$2:D2. In cell D4, the “uniques” range would change to $D$2:D4. The website explains in details how this formula works.
In doing this exercise, it turns out that there are two parts to coming up with a solution. The first part is figuring out a solution for pulling the required data and the second part is creating a process for the user to follow to produce the report or what is probably called in design lexicon the user experience. The process or user experience has to be easy and painless. I can’t assume a) that these managers will be comfortable with Excel (in this industry, they are not), b) that they will use the exact spelling required in order to pull in the information, and c) that they will act as I do when encountering a problem with the spreadsheet. In my mind, I envision the manager coming in in the morning, pulling down the data, opening up the file with the macro and then the program starts to run itself, populating our internal report and the client report, in about 5 minutes. The only thing the manager would have to do is write in the comments.
So once I got a report set up with formulas to pull in the data, I spent some time thinking about how they will use the file and from there started adjusting the process to make the whole endeavor easier. Hopefully, I did a reasonably good job (preferably stellar but I always need to test how they use the files). I’ll see in the next week.
