SOLVED

Help with subtracting specific columns and copying the formula down correctly

Copper Contributor

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!

 ABCDEFGH
1 Raw data      
2 day 1day 2day 3    
3group 1 566570    
4group 1 536368    
5group 2 657675    
6group 2 627374    
7group 3646465    
8group 3616263    
9group 4716277    
10group 4696075    
11        
12     I want it to be  
13 Calculation  Day 1Day 2Day 3
14group 1 322 B3-B4C3-C4D3-D4
15group 2-12-13-7 B5-B6C5-C6D5-D6
16group 3331 B7-B8C7-C8D7-D8
17group 4-299 B9-B10C9-C10D9-D10
2 Replies
best response confirmed by Mar777iam (Copper Contributor)
Solution
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
Thank you so much! I used the index function and it worked like a charm!
1 best response

Accepted Solutions
best response confirmed by Mar777iam (Copper Contributor)
Solution
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

View solution in original post