Nov 02 2021 09:24 AM
I have a 7000 row spreadsheet that I have to manually update every 10 rows. It is a simple update in column H that requires a new value from column I every 10 rows.
Example: Column H
Row10 = Column D - $I$row9
Row 11 = Column D - $I$row9
..........
Row 19 =Column D - $I$row18
Nov 02 2021 09:30 AM
You write "every 10 rows", but from row 10 to row 19 is only 9 rows...
Nov 02 2021 09:37 AM
Nov 02 2021 09:48 AM
Yes, 10 ... 19 is 10 rows in total, but if you want the formula to change in row 10, row 19, row 28, row 37 etc., that is every 9 rows, not every 10 rows.
Nov 02 2021 09:48 AM
It would also mean that the conditional formula I gave you earlier is incorrect...
Nov 02 2021 09:56 AM
Nov 02 2021 09:59 AM
Row 10 has D10-I9
...
Row 18 has D18-I9
Row 19 has D19-I18
The jump is 9 rows, not 10 rows.
Nov 02 2021 10:31 AM
D E F G H I
8/17/2021 | 444.23999 | 444.959991 | 440.850006 | 444.04 | 444.040009 | 92673900 | 10.85 | 432 |
8/18/2021 | 442.959991 | 444.630005 | 438.920013 | 439.18 | 439.179993 | 89351900 | 8.92 | 431 |
8/19/2021 | 436.269989 | 441.140015 | 436.119995 | 439.86 | 439.859985 | 92812200 | 6.12 | 432 |
8/20/2021 | 440.230011 | 443.709991 | 439.709991 | 443.36 | 443.359985 | 71975900 | 9.71 | 432 |
8/23/2021 | 445.160004 | 448.230011 | 443.440002 | 447.26 | 447.26001 | 54973000 | 13.44 | 431 |
8/24/2021 | 447.970001 | 448.540009 | 447.420013 | 447.97 | 447.970001 | 38744700 | 17.42 | 431 |
8/25/2021 | 448.170013 | 449.459991 | 447.769989 | 448.91 | 448.910004 | 40529700 | 17.77 | 431 |
8/26/2021 | 448.609985 | 448.859985 | 446.160004 | 446.26 | 446.26001 | 57829600 | 16.16 | 431 |
8/27/2021 | 447.119995 | 450.649994 | 447.059998 | 450.25 | 450.25 | 77201900 | 17.06 | 431 |
8/30/2021 | 450.970001 | 453.070007 | 450.709991 | 452.23 | 452.230011 | 48357400 | 19.71 | 429 |
8/31/2021 | 452.130005 | 452.48999 | 450.920013 | 451.56 | 451.559998 | 59300200 | 19.92 | 429 |
9/1/2021 | 452.559998 | 453.109985 | 451.549988 | 451.8 | 451.799988 | 48721400 | 20.55 | 429 |
9/2/2021 | 453.320007 | 454.049988 | 451.910004 | 453.19 | 453.190002 | 42501000 | 20.91 | 432 |
9/3/2021 | 451.980011 | 453.630005 | 451.549988 | 453.08 | 453.079987 | 47170500 | 20.55 | 436 |
9/7/2021 | 452.709991 | 452.809998 | 450.73999 | 451.46 | 451.459991 | 51671500 | 19.74 | 439 |
9/8/2021 | 450.890015 | 451.670013 | 448.859985 | 450.91 | 450.910004 | 56121700 | 17.86 | 440 |
9/9/2021 | 450.700012 | 452.570007 | 448.720001 | 448.98 | 448.980011 | 57970400 | 17.72 | 440 |
9/10/2021 | 451.040009 | 451.48999 | 445.309998 | 445.44 | 445.440002 | 89848000 | 14.31 | 438 |
9/13/2021 | 448.640015 | 448.920013 | 444.109985 | 446.58 | 446.579987 | 83738600 | 6.11 | 439 |
9/14/2021 | 448.119995 | 448.339996 | 443.220001 | 444.17 | 444.170013 | 78197100 | 5.22 | 436 |
9/15/2021 | 444.619995 | 448.410004 | 443.440002 | 447.88 | 447.880005 | 78792200 | 5.44 | 436 |
9/16/2021 | 447.320007 | 448.359985 | 444.019989 | 447.17 | 447.170013 | 77695700 | 6.02 | 435 |
09/17/21 | 444.92 | 445.37 | 441.02 | 441.4 | 441.4 | 118229200 | 3.02 | 433 |
9/20/2021 | 434.880005 | 436.559998 | 428.859985 | 434.04 | 434.040009 | 166445500 | -9.14 | 426 |
9/21/2021 | 436.529999 | 437.910004 | 433.070007 | 433.63 | 433.630005 | 92526100 | -4.93 | 422 |
9/22/2021 | 436.049988 | 440.029999 | 433.75 | 437.86 | 437.859985 | 102350100 | -4.25 | 421 |
9/23/2021 | 439.850006 | 444.890015 | 439.600006 | 443.18 | 443.179993 | 76396000 | 1.60 | 422 |
9/24/2021 | 441.440002 | 444.670013 | 441.209991 | 443.91 | 443.910004 | 62025800 | 3.21 | 423 |
9/27/2021 | 442.809998 | 444.049988 | 441.899994 | 442.64 | 442.640015 | 61371100 | 18.90 | 423 |
9/28/2021 | 439.690002 | 440.040009 | 432.940002 | 433.72 | 433.720001 | 130436300 | 9.94 | 421 |
9/29/2021 | 435.190002 | 437.040009 | 433.850006 | 434.45 | 434.450012 | 82329200 | 10.85 | 419 |
9/30/2021 | 436.019989 | 436.769989 | 428.779999 | 429.14 | 429.140015 | 140181200 | 5.78 | 417 |
10/1/2021 | 430.980011 | 436.029999 | 427.230011 | 434.24 | 434.23999 | 129240100 | 4.23 | 418 |
10/4/2021 | 433 | 433.959991 | 426.359985 | 428.64 | 428.640015 | 128570000 | 3.36 | 416 |
10/5/2021 | 430.23999 | 435.48999 | 429.390015 | 433.1 | 433.100006 | 90682500 | 6.39 | 415 |
10/6/2021 | 429.269989 | 435.119995 | 427.540009 | 434.9 | 434.899994 | 113032200 | 4.54 | 416 |
10/7/2021 | 438.390015 | 441.679993 | 438.200012 | 438.66 | 438.660004 | 72437500 | 15.20 | 416 |
10/8/2021 | 439.480011 | 439.890015 | 437.190002 | 437.86 | 437.859985 | 74492900 | 14.19 | 417 |
10/11/2021 | 437.160004 | 440.26001 | 434.619995 | 434.69 | 434.690002 | 65233300 | 17.62 | 418 |
So every 10 rows, Column H uses a new Column I for the next 10 rows. So date 8/30 uses the I value from 8/27 of 431 in column H for 8/30 thru 9/10. Then on 9/11 the next 10 days use 444 in the Column H formula so 9/13 Column D is 444.1099 and column H uses 444.1099 - 438 for the resultant 6.11.then 9/14 Column H uses Column D 443.22 -438 for resultant 5.22 and so on until 9/27 when Column I becomes 423. I hope this clarifies my dilemma.
Nov 02 2021 10:46 AM
Nov 02 2021 01:44 PM
I'm afraid that doesn't help at all; it is very difficult to read. Please attach a sample workbook.
Nov 02 2021 03:47 PM
Nov 02 2021 04:02 PM
Drag the workbook from File Explorer into the area below the box where you enter your reply. It says "Drag and drop here or browse files to attach".
Nov 02 2021 04:07 PM
Nov 02 2021 04:13 PM
Thanks. I'm still confused.
Why does H2 have =D2-$I$2, while H12 has =D12-$I$11 and H22 has =D22-$I$21 ?
Shouldn't H2 have =D2-$I$1 ?
(And why does H1 have =D1-$I$7218 ?)
Nov 02 2021 04:36 PM
It's past midnight where I live, I'm off to bed now.
The formula in H12 could be
=D12-INDEX(I:I,10*QUOTIENT(ROW(H12)-2,10)+1)
This can be filled down.
For H1:H11 it'll return different results than your formula.
Nov 02 2021 05:58 PM
SolutionNov 02 2021 05:58 PM
Solution