Forum Discussion
Solved
Excel subtraction
Hi all,
I'm writing because I would like to know if there is a fast way to do the following thing using excel. Let's say I have 10 rows (from 2 to 11) and one column(B) filled with numerical data...what I have to do is for the column B: B2-B3, B2-B4....B2-B11 and then B3-B4, B3-B5...B3-B11 and so on until B10-B11. I need to do this kind of progressive subtraction of a large set of data...thus a quick way to do this would be very helpfull!
Thanks in advance. Marco.
Hi Marco,
In general that's combination of absolute and relative references like formula
=$B$2-$B3
copied down till end of you range.
How to modify the formula depends on where and how you'd like to show result. For example, to fill this matrix
where in first row is the index from which we start substruction the formula in C2 could be
=IF(ROW()<=C$1,0,OFFSET($B$2,C$1-2,0)-$B2)
which we copy into all other cells. OFFSET says from which number we substruct in particular column.
Please see attached file
I'm sorry, I used a word that could cause misunderstandings, thus I edited the message. Just forget the word "factorial". What I mean is that Iwant to subtract all the values I have in a column without repetitions as the example I explained above. I have 10 rows (from 2 to 11) and one column(B) filled with numerical data...what I have to do is for the column B: B2-B3, B2-B4, B2-B5, B2-B6, B2-B7, B2-B8, B2-B9, B2-B10, B2-B11 and then B3-B4, B3-B5, B3-B6...B3-B11and so on until B10-B11.
Hi Marco,
In general that's combination of absolute and relative references like formula
=$B$2-$B3
copied down till end of you range.
How to modify the formula depends on where and how you'd like to show result. For example, to fill this matrix
where in first row is the index from which we start substruction the formula in C2 could be
=IF(ROW()<=C$1,0,OFFSET($B$2,C$1-2,0)-$B2)
which we copy into all other cells. OFFSET says from which number we substruct in particular column.
Please see attached file
Hi Marco,
Please clarify what do you mean under factorial here. For the first set results for B2*B3; B2*B3*B4; etc. Or multiplication of all above or what?
