Using concatenate() and if() to improve charts and graphs

If you look at the example I discussed last week, specifically the Present_Pie and Present_Line worksheets, you might notice that when making pie charts and the single-value line graph, I had three columns for the source data.

This is significant, because in most cases you’ll only have two columns: one for the axis/category labels, and one for the data itself. After all, that’s all the input the chart or graph will accept.

The reason I have three is that I’m using the third column, which I inserted between the other two columns, to create much more useful label by using concatenate(), and a more readable one by using if(). Continue reading Using concatenate() and if() to improve charts and graphs

Project: Statistics from daily downloaded data

One thing I do every month is compile statistics on how many questions have come in to our chat reference service from various libraries. On the one hand, these statistics are automatically generated, so there’s no fussing or worry about whether they were recorded accurately. On the other hand, downloading a month’s worth of statistics one day at a time makes it easy to skip a day, or download one day twice.

An additional complication is that new libraries sometimes show up, and they need to be added to the list. I’m doing this project now because it’s a great example of the IF() techniques I highlighted in the last two posts. It also pulls together a lot of things I’ve talked about in the past, so I’ve supplied links if you want to know more about the details or how-to of any step. Continue reading Project: Statistics from daily downloaded data

Adding exemptions to checking for missed data

In step 6 of last week’s post I suggested that you might want to just delete the formula from any cell that was marking a valid cell as “check”, so you could easily see when you had checked all of them. That’s fine whenever you’re doing a one-time check, but if you’re using the same sheet every month and just adding data to it each time, that could get tedious if you have to check the same things over and over.

There are a number of different ways to tell your spreadsheet not to mark valid missed dates, such as work holidays. And, as should come as no surprise by this point, all of them depend on an IF() statement. Continue reading Adding exemptions to checking for missed data

Finding missed data

Most of the time when doing time-based statistics, you’re supposed to have statistics for every unit of time you’re reporting on. That is to say, every hour for hourly statistics, every day for daily ones, every month for monthly, and so on. There’s a little trick I use to make sure no chunk of time has been accidentally skipped, and like many of my tricks it relies on the IF function.


This says that if the difference between the current time unit and the one in the previous row isn’t equal to one, it should be flagged for checking. This works because dates are stored as numbers, so they can be added and subtracted, and one day is equal to 1. Continue reading Finding missed data