Reshape instead of Fill-Down condition

Originally published on Thu, 2 Mar, 2017 at 6:59 AM

Fill is used to generate multiple values from an existing formula. It extends a user-defined formula to multiple cells with appropriate modifications in the formula. One advantage worth mentioning in SheetKraft's Fill operation is dynamic inclusion of new data into the formula i.e. even if new data are added to the table after defining the Fill operation, they can be operated upon without setting up Fill again. But there is a possibility where the cell with a existing formula may refer to a NULL value cell or the value is not calculated, where it gives and incorrect Output.In such condition we can use the reshape condition.

For instance, look at the image below the D5 cell has a existing formula according to which the cell D6 to D8 are populated using the Fill-down condition. Here the fill-down is based on the cells A5,B5 and C5, which will give a correct output,only if all the condition are satisfied.

Cell E5, i.e. the cell's under Reshape header are not dependent on any such per-existing formula based cell, this gives the desired output as well.

The above condition for fill-down can give undesired output when the cells A5,B5 and C5 will be empty, i.e. there is no data in them. Where,the D5 cell will be populated irrespective to this condition, which will give a unsuitable output.

In-order to remove such errors you can use Reshape condition. Reshape doesn't require any such formula based cell like Fill-Down.

Reshape returns an array of desired size produced by arranging the values of the Input array.In this the Rows are accessed top to bottom.

Therefore as the cell A to C are empty the value returned in the D5 cell is incorrect, where as it should be #NULL, but as the it doesn't give this error it moves to the cell below where the Fill-down Function is used. Whereas the Reshape condition gives #NULL error in its first cell itself which is a correct output.