Is there a function to subtract from a previous row?

BEDE

New Member
Joined
Mar 29, 2024
Messages
34
Office Version
  1. 365
Platform
  1. Windows
In a spilled column how does one subtract by row?

for example

10 start
11: 11-10 = 1
12: 12-11 = 1
14: 14-12 = 2
16: 16 - 14 = 2
19: 19-16 = 3

Is there a function to achieve this?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Try:
Book2
AB
110
2111
3121
4142
5162
6193
Sheet1
Cell Formulas
RangeFormula
B2:B6B2=A2:A6-A1:A5
Dynamic array formulas.
 
Upvote 0
What solution are you looking for?
Hello,
The columns are spilled, I want to to subtract by row.

doing a byrow(a12#,lambda(x, x-x)) doesn't work
doing a SCAN(0,A12#,LAMBDA(X,Y,Y-X)) sort of works

DROP(A12#,1)-DROP(A12#,-1) works too but creates other problems

I'm just trying to see if there's a function that actually subtracts by row
 
Last edited:
Upvote 0
...creates other problems
You need to give us the full picture. Stating "there's a problem" or "not the solution you're looking for" is not helpful. Be specific.
 
Upvote 0
Your DROP formula is the one I was going to give you, but since that is not what you want, does this do what you want?

=DROP(SCAN(,A2#,LAMBDA(a,x,OFFSET(x,-1,)-x)),1)
 
Upvote 0
Solution
Your DROP formula is the one I was going to give you, but since that is not what you want, does this do what you want?

=DROP(SCAN(,A2#,LAMBDA(a,x,OFFSET(x,-1,)-x)),1)
It's funny I was just looking at offset, summing every n'th row on excel jet. Your fomula returned a Ref# but it was on the money none the less. BYROW(A12#,LAMBDA(X,OFFSET(X,1,,1,)-X)

Thank you, and Thank you Cubist for the quick responses.

Happy New Year.
 
Upvote 0

Forum statistics

Threads
1,225,749
Messages
6,186,802
Members
453,373
Latest member
Ereha

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top