How to dynamically create a cumulative overall total based on a non-cumulative categorical column in excel
<p>Slightly wordy title but here goes</p>

<p>I have a grid in excel which includes 3 columns (media spend, marginal revenue returns &amp; media channel invested in) and I want to create the column below called desired cumulative spend</p>

<p>The reason the grid is structured in this way it does is that it represents an optimised spend laydown ordered by how much of each media channel's budget should be invested in until the marginal returns diminish such that it should be substituted for another media channel. </p>

<p>It is possible that this substitution can then be reversed back to the original channel if the new channel has a sharply diminishing curve, such that all marginal benefit associated to the new channel diminishes and the total spend level still means it is mathematically sensible to switch back to the original curve (maybe it has a lower base level but reduces less sharply). It is also possible that at the point in which the marginal benefit associated to the new channel diminishes, the best next step is to invest in a third channel.</p>

<p>The desired new spend column has two elements to it</p>

<ol>
<li>it is a simple accumulation of spend from row to row when the<br>
media channel is constant from row to row</li>
<li>it is a slightly more tricky accumulation of spend when the media
channel changes - then it needs to be able to reference back to the
last spend level associated to the channel which has been
substituted in. For row 4, the logic I am struggling with would need
to the running total from row 3 plus the new spend level associated
to row 4 minus the spend level the last time this channel was used
(row 2)</li>
</ol>

<hr>

<pre><code>  |spend  | mar return | media | desired cumulative spend                  |
  |------ |----------- |-------| ----------------------------------------- |
1 | £580  | 128        | chan1 | 580                                       |
2 | £620  | 121        | chan1 | 580+(620-580)                             |
3 | £900  | 115.8      | chan2 | 580+(620-580)+900                         |
4 | £660  | 115.1      | chan1 | 580+(620-580)+900+(660-620)               |
5 | £920  | 114        | chan2 | 580+(620-580)+900+(660-620)+(920-900)     |
6 | £940  | 112        | chan2 | 580+(620-580)+900+(660-620)+(920-900)+(940-920) |
</code></pre>