Forum Discussion
Solved
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 |
- 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
- 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!
