Last week, I showed you the simplest form of the XLOOKUP function. Though highly useful, it’s only really useful if you find exact matches. If you’re looking at an array of data – e.g. pay averages for age groups instead of specific ages – you need to help it a little. To this end, the function has a further three arguments. In order, the ones I’ve found useful are:
Category: Microsoft Excel
-
Excel: Looking up data continued
-
Excel: Looking up data
A while back, I showed you how I go about creating tables if average values using the
(more…)UNIQUEandAVERAGEIFfunctions. While those tables are valuable in and of themselves, I use them to compare the pay an individual in my group gets with people in the same part of the organization. -
Excel: Calculating days, months, and years
One thing I find myself needing to do on a relatively regular basis, is calculate the number of days, months, or years between two given dates. Like with so many other things, Excel has a function for this, too. It’s intuitively named
(more…)DATEDIF, and here’s how you use it. -
Excel: Find average based on multiple criteria
Last week, I showed you how to find an average based on a single criterion. That works well enough, but what if you want to find an average based on multiple criteria? In that case, the AVERAGEIF-function will fall short. In its place, you must use the AVERAGEIFS-function. This time, we want to find the averages for both ALPHA and BRAVO.
(more…) -
Excel: Finding averages based on a specified criterion
One of the many things I do in my capacity as a union representative is to assemble my own statistics based on raw numbers I get from my employer. One of the stats I calculate is the average pay in each section. As you might remember, last week I assembled a list of forty seven unique codes.
(more…) -
Excel: Finding unique values
In complex datasets, finding the unique values can sometimes be a bit of a challenge. While you can often simply remove duplicates, if you intend to create a lookup-table, that isn’t an option, and you need to do it differently. Enter the UNIQUE-function.
(more…) -
Excel: Adding multiple axes in a chart
One of the many things I do as a union representative, is negotiate wages with management. I argue my point using a number of tools, not least of which is the change in pay over time. I have one set of data which is what we use when negotiating, which represents the estimated change. I have another set which represents the actual change. I wanted to show not only these in relation to each other, but also the relative difference as it changes over time.
(more…) -
Excel: Visualising pay differences
As I’ve mentioned more than once previously, I’m a union representative. Among my many duties in this role is wage negotiations. While we negotiate collectively, I also make sure to dive into the numbers to look for the unexplained outliers, and to have them addressed.
(more…) -
Excel: Allow dividing formulas to fail gracefully
I’m sure I’m not alone in having had this happen: I was using Excel to make some quick calculations, and got
(more…)#DIV/0!-errors for some rows because one of the fields I’d been dividing by was empty. Sometimes you don’t have all the information you need for every single row, but you still want to have a standardised calculation to run where you do have the information you need. -
Excel: Automatically set width and height
If I’ve been there once, I’ve been there a thousand times; I’ve got a spreadsheet in excel with varying widths and heights that don’t fit the data. Now, sure, you could go in and correct it manually – but that’s not only boring, it’s a pain it the behind. Luckily, there are keyboard shortcuts to accomplish it. Here’s how:
(more…)