Skip to main content

Running Totals in Excel

Excel does not have a running total feature or function built in.  All the examples I found on the web to do running totals included VBA code. Not that I have anything against VBA but I thought there should be a way to do running totals with built in worksheet functions.

Enter our one of our favorite functions: OFFSET().  But first, what is a running total?

Running Total Example

A running total is when you have a list of Values and you want to total of the current Value with the Previous values.  Wikipedia states that a running total is "summation of a sequence of numbers which is updated each time a new number is added to the sequence, simply by adding the value of the new number to the running total."

The key to getting the SUM() correct is getting the Range correct.  For a given Range of Values, start with the First number and SUM() until you get to the current row.  You can do this by using the OFFSET() function and taking advantage of Excel's table features to get the column range.

[vb] OFFSET ( cell reference, rows, columns, [ height ], [ width ] ) [/vb]

In the above case the Running Total column's formula becomes:

[vb] =SUM( OFFSET( [Values], 0, 0, ROW() - ROW([Values]) + 1, 1 ) ) [/vb]

[Values] is the Column we want the running total for.

rows = 0 and columns = 0 because we want to start at the very first cell of [Values]

[width] = 1 because we want only the [Values] column

[height] = ROW() - ROW([Values]) + 1, this is the magic line.

To get the height we have to figure out our current Row number, subtract off the starting Row of [Values] then add 1.  ROW([Values]) gives us the starting row of the column and ROW() gives us the current row.  For example, if the Table starts on row 3 (headers are on row 3) then the column [Values] starts on row 4.  The height of the very first cell in [Values] is:

[vb] ROW() - ROW([Values]) + 1 = 4 - 4 + 1 = 1 [/vb]

The height of the 3rd cell in the [Values] column is:

[vb]ROW() - ROW([Values]) + 1 = 6 - 4 + 1 = 3 [/vb]

Offset Function in Excel

Running Total

Comments

Popular posts from this blog

Json for jqGrid from ASP.Net MVC

jqGrid takes a specific format for its json (taken from jqGrid documentation): [js]{ total: "xxx",page: "yyy", records: "zzz", rows : [ {id:"1", cell:["cell11", "cell12", "cell13"]}, {id:"2", cell:["cell21", "cell22", "cell23"]}, ... ]}[/js] The tags mean the following: total - Total number of Pages. page - Current page Index. records - Total number of records in the rows group. rows - An array with the data plus an identifier. id - The unique row identifier, needs to be an int from what I have found. cell - An array of the data for the grid. The ASP.Net MVC framework has the JsonResult response type which we can use to populate the jqGrid. As an example I created a Person model and a method to return some data: [csharp] public class Person { public int ID { get; set; } public string Name { get; set; } public DateTime Birthday { get; set; } } public I

Remote Controlled RoboTank

This is my version of the ever popular to build RoboTank. It uses an Arduino Mega 2560 with the AdaFruit motor shield and an XBee S1 to communicate to the DFRobot Gamepad. The sketch for the RoboTank makes use of the AFMotor.h to drive the motors and includes a serial parser to read and process the commands coming from the Gamepad. Robotank-Sketch.zip DFRobot Wireless Joystick

FileSystemWatcher With the BlockingCollection

While working with the FileSystemWatcher I found that if too many files were created the built in buffer will overflowed and files will be skipped.  After much research I found out about the Producer-Consumer Problem .  Then I found that .Net 4 has the BlockingCollection which helps solve the issue.  But how to use it with the FileSystemWatcher? On StackOverflow I found  Making PLINQ and BlockingCollection work together .  I'm not so interested in the PLINQ issue but this is a great example of using The BlockingCollection with FileSystemWatcher. [csharp] using System; using System.Collections.Concurrent; using System.Collections.Generic; using System.IO; using System.Linq; using System.Threading; namespace ConsoleApplication4 {     public class Program     {         private const string Folder = "C:\\Temp\\InputData";         static void Main(string[] args) {             var cts = new CancellationTokenSource();             foreach (var obj in Input(cts.Token))