Excel - finding the highest cumulative value

Yella Fella · 7 Mar 2023 at 14:45

Hey all,

I need some help in working out a column of numbers which I'm adding up a cumulative total... so let's say the column has 30 expected values and I only have the 3rd so far e.g. 3000, 3000 and 4000. This is displayed as, which I have done already:

3000
6000
10000

I need to display in a cell the latest cumulative total spend rather than manually entering the last number. Is this possible?

I'm looking at ISBLANK formula or similar as I know there are dashes displayed if no numbers. Working in some sort of if loop statement looking down the column. Perhaps check if cell is 0 then look at the previous number if above 0 then display. Something along those lines. All help is appreciated. Thanks.

KompuKare · 7 Mar 2023 at 14:50

I think you'll have to give us a screenshot / table, but for the ones you listed, then max(range) would do it. Or if say, column A had date times, B had the numbers and C a sum formula, then a VLOOKUP with MAX(A:A) might do?
Something like =VLOOKUP(MAX(A:A), A:B, 3,FALSE)

wesimmo · 7 Mar 2023 at 14:52

If you want to do it an adjacent column and assuming the data starts in cell A2..

In B2 SUM(A$2:A2) then drag down.

dazzerd · 7 Mar 2023 at 15:12

Original	Cumulative Sum
3000	3000
3000	6000
4000	10000

In cell b2:SUM(A$2:A2) and drag down

Yella Fella · 7 Mar 2023 at 15:20

Sorry all, let me try again and be a bit more clear. So I've done the following... assuming 30 rows of data:

A B C
Dates Cost Cumulative
05-Feb 3000 3000
12-Feb 3000 6000
19-Feb 4000 10000

I wanted to write in another cell on another sheet that just takes the latest highest cumulative number in column C. In this case, that cell will show 10000 and then the next highest value when the cells get updated for 26-Feb and then 02-Mar and so on.

Is there a way of looking at the column and just noting the latest highest number value essentially? Cheers all.

Yella Fella · 7 Mar 2023 at 15:23

KompuKare said:
I think you'll have to give us a screenshot / table, but for the ones you listed, then max(range) would do it. Or if say, column A had date times, B had the numbers and C a sum formula, then a VLOOKUP with MAX(A:A) might do?
Something like =VLOOKUP(MAX(A:A), A:B, 3,FALSE)

Actually, the MAX(range) does exactly what I need. Sorry all. Sometimes I over complicate things ha.

wesimmo · 7 Mar 2023 at 15:24

=MAX(range)?

Yella Fella · 7 Mar 2023 at 16:34

wesimmo said:
=MAX(range)?

As in my cell range ha =MAX(C2:C30) etc

wesimmo · 7 Mar 2023 at 16:49

Your cumulative column being C and assuming no negative numbers then yes.