Up and down the country, businesses are running on Excel, instead of using a proper business intelligence (or even management information) system. The one I look after is no different but, as I pieced together yet another spreadsheet last weekend, I learned a few Excel tips that might be useful to share…

=SUMIF()

I’ve been trying to pull together a resource forecast in order to work out how quickly to grow my team. The approach I look was to list all the projects we have coming through, with headcount requirements split out by grade, then to total each column based on the grade of staff required.

Seems fair enough, but the trick to making this work is reading a cell and then only including its value in the total if a condition is met (e.g. the indicated grade matches the one I’m adding up).

Stack Overflow came to my rescue, describing Excel’s SUMIF() function

In my case, the formula was something like:

=SUMIF(E4:E148,E154,F4:F148)

Where E4:E148 contained the grades of people for each identified project, E154 contained the grade I was looking for (e.g. Exchange Designer) and F4:F148 were the numbers of people needed for each project that month. Repeat for each grade, and then for each month, and a table of resource requirements can be built up…

There may be better ways to do this, but it will save me some time adding up the totals each time I revisit the task list…

More margins…

Of course, knowing how many people I need is one thing – making some crude assumptions about the likely revenue they might attract to see if I’m close to my numbers for the year is the next question I’ll be asked.

Last week, I blogged about the difference between mark-up and margin, and this week I needed to put that into practice.  I found a forum post that explained the formula (sale price = 100/1-margin * original cost), so I put that into practice, multiplying by a day rate, an assumed number of working days in the month and the total of that grade of person:

=(D165*(1/(1-D174)))*D175*F154

Which translates to:

=(dayrate*(1/(1-margin)))*number of days*number of people

Displaying data in 1000s

The last part was displaying data. Some of the revenue numbers I ended up with are big – and I’m only interested in 1000s of pounds, so I needed to adjust the formatting of the results.  The trick here is to use a custom number format on the cell of 0, (zero comma) for thousands (or 0,, can be used for millions). Add a K or an M on the end for units, and a currency symbol up front too. You can also add a decimal point using 0.0, (e.g. £0.0,K for £1500 to be displayed as £1.5K) or, if the numbers get into the millions, then try something like £0,000,K.