Excel Table Functionality: lookup previous row

Question

Hi All,

I have a problem that I want to solve using the Table functionality. Please see screenshot

In the first column I have the turnover and in the second column the running total of the turnover.

What I to achieve, in my table i.e. in column H is what I have in column J, see formula:

=IF(Table1[@[Running Turnover]]>Table1[@[Fixed Amount]],Table1[@[Running Turnover]]-Table1[@[Fixed Amount]],0)-SUM($J$3:J3)

This formula is copied down from J3 to J15

The logic is as follows:

Ifd running total > Fixed amount, choose the amount in column D. Exception: if this first instance of the running total > Fixed amount occurs make the following calculation:

Turnover - (Fixed amount – Running turnover of the previous row).

So in this case in J12 the answer is 46 – (350 – 332) = 28

Can you help me building the required the formula, please? Or is this not possible at all in the Table function of Excel, as I have been working on it for some time now.

Please see the link to the spreadsheet here: https://docs.google.com/spreadsheets/d/1fpWnjJOcn0DNxl7HpNBrmHIttrH4mL8p/edit?usp=drive_link&ouid=110294040238289903125&rtpof=true&sd=true

Thanks,

 Excel

Naveen

Answer 1

Hi,

Enter this formula in cell H3 and drag down

=IF([@[Running Turnover]]>[@[Fixed Amount]],[@[Running Turnover]]-[@[Fixed Amount]],0)-SUM($H$2:H2)

Hope this helps.

Answer 2

Hi Ashish,

Thanks for your time and efforts.

The formula you suggested is the same formula I put in my question, right? The reason why I am not using my formula is that it gave a circular and did not use the second row as it was the header. However, I tried it and it worked.

Thanks,

Naveen

Answer 3

You are welcome.