Done In 60 Seconds – Cumulative Sums

Have an ordered list of items, each with an assigned value such as revenue or cost, which you need to analyse and understand? Perhaps you need to know how these numbers are adding up in order to be alerted to business limits (budgets, deadlines etc,) being approached or reached. Here’s how.

 

Let’s say we have some monthly marketing costs in a spreadsheet. Suppose we want to know how much we have cumulatively spent at any given month of the year, so that we can see, for example, at what point we hit a certain budget level eg. £500k. There are a number of unwieldy ways of doing this (example, put B2 into C2 and then =C2+B3 into C3 etc) but here’s the best.

 

Type =SUM($B$3:B3) into cell C3

 

This asks Excel to add up all the cells starting from B3 down to the current row (in the first case this is also B3). So in cell C4, you should have =SUM($B$3: Excel-trainingB4). Pay special attention to the dollar signs – you will not be able to copy the formula down if these are wrong!

 

Copying the formula down for the remaining 10 lines will give (see yellow):

 

 

This trick also calculates cumulative percentages nicely (on the right, in blue).

 

 

The EwB Team