How to drag and and continue formula sequence?

[Yamasaki450]

Hey guys I need your help again.

I want to drag and fill the rest of the cells with formulas and continue formula sequence...

In D2 is formula =A2-A3
In E2 is formula =A2-A4
In F2 is formula =A2-A5

So in G2 i want formula =A2-A6 and in H2 i want =A2-A7 and so on... (see the picture)

I there a way to do that without manually entering formula in each cell?

Thanks.

 

[Rick Rothstein]

You could use this formula to produce those results...

=LET(rng,A2:A500,@rng-INDEX(rng,SEQUENCE(,COUNT(rng)-1,2)))

 

[StephenCrump]

Or: =A2-TRANSPOSE(A3:A500)

 

[Yamasaki450]

Or: =A2-TRANSPOSE(A3:A500)

Awesome. This is exactly what i need... Thanks a lot Stephen.

Great forum by the way

 

[Yamasaki450]

You could use this formula to produce those results...

=LET(rng,A2:A500,@rng-INDEX(rng,SEQUENCE(,COUNT(rng)-1,2)))

I tried this too but it doesnt work in my version of excel...

 

[Rick Rothstein]

I tried this too but it doesnt work in my version of excel...

According to the documentation that I checked, you have both the LET and SEQUENCE functions in your 2021 version of Excel, and INDEX and COUNT are old legacy functions, so I do not see why my formula did not work. But no matter as @StephenCrump's formula is much, much better than mine.

 

[StephenCrump]

... so I do not see why my formula did not work.

Nor me.

@Yamasaki450, when you say "it doesn't work", does that mean the formula gives you an error? Or the wrong numbers?

The formula needs to be entered exactly as written - my version of Excel "helpfully" suggests a variation which will produce only a single value, not what we want. So I need to say "No" to that suggestion.

The "@rng"construction also means that the formula must be entered in line with your start value, i.e. somewhere in row 2 in this case. If not, you'll either get incorrect numbers or an error.