I want to calculate the difference of numbers that are inputted in a Google sheet row like this:

Row 1: 35 | 37 | 39 | 38 and So on...

I found a formula that can get the last entered number in Row 1:

=IFNA(LOOKUP(1, INDEX(1/(Row 1:1<>"")), Row 1:1),"")

However, I need to sum these values like this:

Row 1 Output: (37-35) (39-37) (38-39) = (2 2-1)=3

Any idea how to solve this?

Thanks!

CodePudding user response：

Use INDEX/COUNTA to get the last column and OFFSET to shift the range by -1:

=LAMBDA(rg,SUMPRODUCT(rg-OFFSET(rg,0,-1)))(INDEX(1:1,2):INDEX(1:1,COUNTA(1:1)))

CodePudding user response：

Give a try to following formula-

=SUM(INDEX(B1:INDEX(1:1,COUNTA(1:1))-A1:INDEX(1:1,COUNTA(1:1)-1)))

CodePudding user response：

Row 1 Output: (37-35) (39-37) (38-39) = (2 2-1)=3

Notice that the middle numbers (37 and 39) cancel out so you just need to subtract the first number from the last one

38-35 = 3

So this should be enough

=index(1:1,count(1:1))-A1