# Dynamic insert function

*It is a requirement that I use Excel to solve this issue.*

In col A I have 0s and 1s with various quantities of 0s between the 1s. Every time a 1 appears I want the difference between two numbers given in two columns next to my binary column. However I wish to get the results from this calculation stated next to the previous 1.

I'd cope with different software, but how do I achieve this with Excel?

## Answers

=IF(A4=1,OFFSET(B4,MATCH(1,A5:A$1000,0),0)-OFFSET(C4,MATCH(1,A5:A$1000,0),),"")

in D4 and copied down to suit seems to work.

Edit:

=(IF(A$=1, ,"") is as in: IF(logical_test,value_if_true,value_if_false) where value if false is (*blank*), expressed as "".

The value_if_true is the difference between ColumnB and ColumnC values, each ‘located’ from an OFFSET function as in =OFFSET(reference,rows,cols,height,width).

references are to the appropriate column for the row into which the formula is inserted (ie B4 and C4) from which the values required are ‘south’ by a variable amount.

MATCH, as in =MATCH(lookup_value, lookup_array, [match_type]) is to determine the extent of the offset on a case-by-case basis. In reverse order, the parameters here are match_type = 0 (to require an exact match) and lookup_array is as much of ColumnA as required. Initially chosen as up to Row1000 (by A$1000) but can be extended as far as necessary, subject to row limit for the relevant Excel version.

The first parameter lookup_value) is of course 1 since that is the flag for the rows that contain the values to be subtracted.

Without a $ between A and 5 in the MATCH functions the size of the array automatically decreases (top cell row reference increases) as the formula is copied down, hence finds the *next* instance (rather than the same one over and over again).