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?
in D4 and copied down to suit seems to work.
=(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).