Discussion Re: Range changing in increments in Excel
https://techcommunity.microsoft.com/t5/excel/range-changing-in-increments/m-p/2558430#M107041
<P><LI-USER uid="1105924"></LI-USER> </P>
<P>Let's say the formula =SUM(DataSheet!B2:B138) is in cell D2 of the summary sheet.</P>
<P>Change it to</P>
<P> </P>
<P>=SUM(OFFSET(DataSheet!$B$2:$B$138, 137*(ROW()-2), 0))</P>
<P> </P>
<P>or</P>
<P> </P>
<P>=SUM(INDEX(DataSheet!B:B,137*(ROW()-2)+2):INDEX(DataSheet!B:B,137*(ROW()-2)+138))</P>
<P> </P>
<P>Then fill down.</P>Sat, 17 Jul 2021 10:44:30 GMTHans Vogelaar2021-07-17T10:44:30ZRange changing in increments
https://techcommunity.microsoft.com/t5/excel/range-changing-in-increments/m-p/2558396#M107040
I need some help. I’m summarising data from one sheet into another, the amount of data points is in the thousands. In my summary sheet I’m summarising around 250 data rows into one row on a seperate sheet and when I drag it down the column to summarise the next range, it doesn’t follow it. For example I want to summarise data from B2:B138, such as SUM(B2:B138). When I go down a column the formula then changes to be SUM(B3:B139) rather then being SUM(B139:B275) where the ranges changes by the whole 137 points. I don’t want to re type it as I have a good 50 of these to do. I’m sorry if it’s hard to understand I’m new to excel and suck as explaining it, but for anyone who gets what I’m trying to say, any help would be amazingSat, 17 Jul 2021 10:28:31 GMThttps://techcommunity.microsoft.com/t5/excel/range-changing-in-increments/m-p/2558396#M107040Tbear_beardsley2021-07-17T10:28:31ZRe: Range changing in increments
https://techcommunity.microsoft.com/t5/excel/range-changing-in-increments/m-p/2558430#M107041
<P><LI-USER uid="1105924"></LI-USER> </P>
<P>Let's say the formula =SUM(DataSheet!B2:B138) is in cell D2 of the summary sheet.</P>
<P>Change it to</P>
<P> </P>
<P>=SUM(OFFSET(DataSheet!$B$2:$B$138, 137*(ROW()-2), 0))</P>
<P> </P>
<P>or</P>
<P> </P>
<P>=SUM(INDEX(DataSheet!B:B,137*(ROW()-2)+2):INDEX(DataSheet!B:B,137*(ROW()-2)+138))</P>
<P> </P>
<P>Then fill down.</P>Sat, 17 Jul 2021 10:44:30 GMThttps://techcommunity.microsoft.com/t5/excel/range-changing-in-increments/m-p/2558430#M107041Hans Vogelaar2021-07-17T10:44:30ZRe: Range changing in increments
https://techcommunity.microsoft.com/t5/excel/range-changing-in-increments/m-p/2558465#M107045
Perfect. Can you explain the row situation. And would it be similar if I wanted to do the same along the columns for data going across?Sat, 17 Jul 2021 11:17:53 GMThttps://techcommunity.microsoft.com/t5/excel/range-changing-in-increments/m-p/2558465#M107045Tbear_beardsley2021-07-17T11:17:53ZRe: Range changing in increments
https://techcommunity.microsoft.com/t5/excel/range-changing-in-increments/m-p/2558466#M107046
Never mind found out I just have to take the $ off the B but leave it on the numberSat, 17 Jul 2021 11:22:38 GMThttps://techcommunity.microsoft.com/t5/excel/range-changing-in-increments/m-p/2558466#M107046Tbear_beardsley2021-07-17T11:22:38ZRe: Range changing in increments
https://techcommunity.microsoft.com/t5/excel/range-changing-in-increments/m-p/2558467#M107047
<P><LI-USER uid="1105924"></LI-USER> </P>
<P>Great!</P>
<P> </P>
<P>ROW() returns the row number of the cell with the formula. So for a cell in row 2, ROW() returns 2.</P>
<P>ROW()-2 returns 0 in row 2, 1 in row 3 etc.</P>
<P>137*(ROW()-2) returns 0 in row 2, 137 in row 3, etc.</P>
<P>OFFSET(DataSheet!B$2:B$138, 137*(ROW()-2), 0) is equivalent to B$2:B$138 in row 2, but to B$139:B$275 (that is, 137 rows down from B$2:B$137) in row 3, etc.</P>Sat, 17 Jul 2021 11:26:08 GMThttps://techcommunity.microsoft.com/t5/excel/range-changing-in-increments/m-p/2558467#M107047Hans Vogelaar2021-07-17T11:26:08ZRe: Range changing in increments
https://techcommunity.microsoft.com/t5/excel/range-changing-in-increments/m-p/2559061#M107090
<P><LI-USER uid="1105924"></LI-USER> </P><P>You didn't state what version of Excel you use so I held back because all my work is specific to Excel 365 and sometime beta channel only. It is macro-enabled because it contains VBA utility to upload Lambda functions which are of interest to me but not generally usable. The LET functions work with most Excel 365 installations.</P><P>The formula that is most likely to be usable is</P><LI-CODE lang="excel">= LET(
array, INDEX(data, SEQUENCE(10,Blocksize)),
vector, SEQUENCE(Blocksize,,,0),
MMULT(array, vector))</LI-CODE><P>and least likely</P><LI-CODE lang="excel">= SUMROWSλ(
PIVOTλ(data, blocksize)
)</LI-CODE>Sat, 17 Jul 2021 18:38:46 GMThttps://techcommunity.microsoft.com/t5/excel/range-changing-in-increments/m-p/2559061#M107090Peter Bartholomew2021-07-17T18:38:46Z