I prefer the MAP lambda helper function to other LHFs to code a formula to subtract the previous value (row above) from the current row value in Google Sheets.

Other LHFs include SCAN or BYROW, but the advantage of MAP is we can use multiple arrays.

How do the multiple arrays come in use in this type of calculation?

It will help us when we want to subtract the current row value from the previous row value based on a group column.

We will start with one-column data, which means the grouping is not involved. So we can use either of the said LHFs. Here we go!

Subtracting the Previous Value from the Current Value

In the following screenshot, you can see data spread across several columns. The sample data is in columns B and C.

Columns E and G contain the output of two lambda formulas.

We will start with the data in column C and the result in column G.

I want an array formula to subtract the C2 value from C3 (=C3-C2), the C4 value from C3 (=C4-C3), and so on.

Now see the result in G2:G11, where I’ve used the following array formula in cell G2.

Formula_1 (Map):

={0;map(C3:C11,lambda(r,r-indirect("C"&row(r)-1)))}

The formula subtracts the previous value from the current value row by row.

Feel free to replace the function MAP with BYROW. The rest of the formula, including the LAMBDA, is the same.

Formula_2 (Byrow):

={0;byrow(C3:C11,lambda(r,r-indirect("C"&row(r)-1)))}

But if you use SCAN, you must use it as follows.

Formula_3 (Scan):

={0;scan(,C3:C11,lambda(a,r,r-indirect("C"&row(r)-1)))}

How do these formulas work?

In the formulas, r = current_value, and indirect("C"&row(r)-1) = previous_value.

How Can We Open the Range from C3:C11 to C3:C?

The solution is to find the relative position of the last non-blank cell in column C.

The following formula will do that in Google Sheets.

=ArrayFormula(MATCH(2,1/(C3:C<>""),1))

Then use INDEX to offset that many rows one by one.

So what you want to do is replace C3:C11 with C3:index(C3:C,last_non_blank_cell).

You can remove ArrayFormula while doing so, as it’s not necessary within INDEX.

So, here is the formula to subtract the previous value from the current value in an open range in Google Sheets.

={0;map(C3:index(C3:C,match(2,1/(C3:C<>""),1)),lambda(r,r-indirect("C"&row(r)-1)))}

Subtracting Value from Row above by Group

In addition to the “Amount” column, we will consider the “ID” column too here.

The purpose is to subtract the value from the row above by group.

Please see the range B2:C11 and the output in E2:E11 in the screenshot above.

We require a sorted table.

In the above example, I’ve sorted the table (range) by ID; the group by column.

Here is the E2 formula to subtract the value from the row above by group in Google Sheets.

={0;map(B3:B11,C3:C11,lambda(_,r,if(countif(B2:_,_)=1,0,r-indirect("C"&row(r)-1))))}

How does this formula differ from formula_1 above?

Please see the highlighted part.

Here is the logic behind subtracting the current value from the cell above by group.

Generic Formula: if(running_count_of_occurrence_of_group_column_values=1,0,formula_1)

We can do all this within one MAP formula. That’s what the E2 formula does.

The red highlighted part will return the running count of the occurrence of the IDs.

The countif(B2:_,_) part is specifically for returning the running count of the occurrence of the IDs.

You May Like: Get the Count of Consecutive Occurrences of Values in Google Sheets.