MMULT Running Total

Previously, I showed you how to use the SCAN function to calculate the running total with a single formula (https://bit.ly/360ASF7). However, given only Office Insiders can access it right now, it’s probably not wise to use it in shared workbooks.

Thankfully, I have a solution that will work for most people. It involves MMULT, which I covered a couple of days ago (https://bit.ly/3HCyX7u).

Image 1 features a table of sales data for the first 20 days of January 2022. Each Sales value is accumulated and returned under Running Total.

The formula breakdown is shown in images 2, 3 and 4.

Inside the MMULT 𝗮𝗿𝗿𝗮𝘆𝟭 argument, a matrix is generated with incremental values displayed vertically and horizontally. A conditional statement checks to see whether each row is less than or equal to each column. For example, row 3 <= column 7 is TRUE and row 18 <= column 13 is FALSE. (Image 2)

Multiplying this matrix by the Sales column generates another. Here, the Sales figures and zeros replace the TRUE and FALSE values, respectively. However, it’s also necessary to transpose it so the numbers build up from the top downwards. (Image 3)

As the Sales column is equal to itself, it creates a column of ones in 𝗮𝗿𝗿𝗮𝘆𝟮 (image 4), which are multiplied by the 𝗮𝗿𝗿𝗮𝘆𝟭 values to return the running total.

The first few calculations are:

1. (762 x 1 = 762) + (0 x 1 = 0)…𝘳𝘦𝘱𝘦𝘢𝘵𝘦𝘥 18 𝘮𝘰𝘳𝘦 𝘵𝘪𝘮𝘦𝘴 = £762

2. (762 x 1 = 762) + (577 x 1 = 577) + (0 x 1 = 0)…𝘳𝘦𝘱𝘦𝘢𝘵𝘦𝘥 17 𝘮𝘰𝘳𝘦 𝘵𝘪𝘮𝘦𝘴 = £1339

3. (762 x 1 = 762) + (577 x 1 = 577) + (701 x 1 = 701) + (0 x 1 = 0)…𝘳𝘦𝘱𝘦𝘢𝘵𝘦𝘥 16 𝘮𝘰𝘳𝘦 𝘵𝘪𝘮𝘦𝘴 = £2040