SOLVED

New Contributor

# Help with subtracting specific columns and copying the formula down correctly

Hello,

I have what seems a simple problem but I cannot figure out how to fix it. Below is a sample of what I want to accomplish. I have data that is placed vertically and I need to subtract the bottom number from the top number for each group. This needs to happen for each group and for each different timepoint. However, when I do B3-B4 and try to drag it down, the next value in excel becomes B4-B5, B5-B6, etc. I want a way to drag the formula down so that instead of B3-B4, B4-B5, B5-B6, etc. it skips the row or recognizes the pattern of B3-B4, B5-B6, B7-B8, etc. I highly appreciate your help!

 A B C D E F G H 1 Raw data 2 day 1 day 2 day 3 3 group 1 56 65 70 4 group 1 53 63 68 5 group 2 65 76 75 6 group 2 62 73 74 7 group 3 64 64 65 8 group 3 61 62 63 9 group 4 71 62 77 10 group 4 69 60 75 11 12 I want it to be 13 Calculation Day 1 Day 2 Day 3 14 group 1 3 2 2 B3-B4 C3-C4 D3-D4 15 group 2 -12 -13 -7 B5-B6 C5-C6 D5-D6 16 group 3 3 3 1 B7-B8 C7-C8 D7-D8 17 group 4 -2 9 9 B9-B10 C9-C10 D9-D10
2 Replies
best response confirmed by Mar777iam (New Contributor)
Solution

# Re: Help with subtracting specific columns and copying the formula down correctly

you could "hard code" it with something like:
=index(B:B,2*row()-25) - index(B:B,2*row()-24)
or use lookups
=XMATCH(\$B14,\$B\$1:\$B\$10,0,1)-XMATCH(\$B14,\$B\$1:\$B\$10,0,-1)
or you could insert 'dummy' lines between each row of the second set so the lines of interest line up and then you could format those 'between' rows to be hidden or small or white on white or use a formula like =IF(ISEVEN(ROW()),B3-B4,"") or ISODD depending on where the table starts

# Re: Help with subtracting specific columns and copying the formula down correctly

Thank you so much! I used the index function and it worked like a charm!