Forum Discussion

nikiw's avatar
nikiw
Copper Contributor
Oct 22, 2021

Formula help

Hello! 

I need help with an excel formula please 🙂

I have data in column A and a running total in column B

I want to know the difference between the figures in column B but the number of blank spaces differs is there a way I can get the difference between the next figure and the last despite the number of blank spaces?

I have done it for C6 and C10 but as there is only 1 blank space on the next equation the formula is wrong if I paste the sum into C12 it does =SUM(B12-B8) which is wrong and needs to be =SUM(B10-B8)


After this I am wanting to put into column D the addition of the figures in coulm A to ensure column C and D are the same but again the length of the equation will differ each time

I hope this makes sense 

Any help is greatly appreciated 

excel
Formulas and Functions
office 365

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 22, 2021

    nikiw 

    Leave C2 empty, obviously.

    Enter the following formula in C3:

    =IF(B3="","",B3-LOOKUP(9.99999999999999E+307,B$2:B2))

    And in D3:

    =IF(B3="","",SUM(INDEX(A:A,MAX((B$2:B2<>"")*ROW(B$2:B2)+1)):A3))

    If you don't have Microsoft 365/Office 2021 you'll have to confirm the formula with Ctrl+Shift+Enter.

    Fill down from C3:D3 to the last row with data,

Resources