Engineers-Excel.com

Using Excel to create a long Excel Formula



Spreadsheet users often come across a situation when a long Excel formula is required to be created. Such formulas often have similar functions that have just one variable which changes. For example, in the batch scheduler tool, we use the OFFSET function to find out which the setup time given 2 batches.

These formulae are often keyed in by hand. Keying in the formula by hand is prone to error and it is also difficult to detect and fix such errors. A more efficient way is to use Excel itself to create such formulae. In this tip, we will see how to do this.

Lets take for example, the formula used in the batch scheduler app. The formula is:

=OFFSET($F$3,MID(B1,1,1),MID(B1,2,1))+OFFSET($F$3,MID(B1,2,1),MID(B1,3,1))

+OFFSET($F$3,MID(B1,3,1),MID(B1,4,1))+OFFSET($F$3,MID(B1,4,1),MID(B1,5,1))

+OFFSET($F$3,MID(B1,5,1),MID(B1,6,1))+OFFSET($F$3,MID(B1,6,1),MID(B1,7,1))

+OFFSET($F$3,MID(B1,7,1),MID(B1,8,1))

In this formula, the function OFFSET is called 7 times, the only change is the difference in the 2nd argument of the MID formula:

=OFFSET($F$3,MID(B1,1,1),MID(B1,2,1))+OFFSET($F$3,MID(B1,2,1),MID(B1,3,1))

+OFFSET($F$3,MID(B1,3,1),MID(B1,4,1))+OFFSET($F$3,MID(B1,4,1),MID(B1,5,1))

+OFFSET($F$3,MID(B1,5,1),MID(B1,6,1))+OFFSET($F$3,MID(B1,6,1),MID(B1,7,1))

+OFFSET($F$3,MID(B1,7,1),MID(B1,8,1))

Instead of typing this manually, Excel can be used to create this function, and ensure that there are no errors in the arguments. Click here to download the file that shows how to do this.

Long Formula

The first column contains the numbers 1 through 7, which are the changing arguments in the MID formula. In the next 2 columns, the 2 MID formulae in each OFFSET function are obtained by simple string concatenation. The individual OFFSET functions are obtained in column D and they are finally "added up" in the column E. The cell E1 is just a copy of cell D1, while the formulae in the other cells of column E add up the string in the corresponding row of column D with the string above it, and inserting a plus (+) sign. The required formula is finally obtained in the cell E7, which can then be used by copying and using Paste Special -> Values.