Reshape instead of Fill-Down condition

Posted under tips on May 02 , 2019 by Saloni Gude

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.

img1

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.

img2



TAGGED:ReshapeFilldown


Recent Posts


Database Table Indexing

January 03, 2020 bySwanand Nirgudkar

For an automated process any operation involving database is relatively expensive since it involves…

Continue Reading

Truncating Database Transaction Log

November 02, 2019 byNidhisha Shetty

Databases are usually in Full Recovery mode. This means that every transaction is retained in the…

Continue Reading

ActivityRunner Not Starting

October 11, 2019 bySwanand Nirgudkar

One may face issue while applying SheetKraft patch wherein ActivityRunner service refuses to start…

Continue Reading

DataFromExcel Fails in Excel 2010

May 02, 2019 byUpasana

Originally published on Wed, 1 Mar, 2017 at 6:13 AM This problem occurs when you try to Import data…

Continue Reading

Let's talk

Address

7th floor, Unit No. 701, C Wing,
Kailas Business Park, S. Veer Savarkar Marg,
Park Site, Vikhroli (W), Mumbai-400079.

 

Phone

+91 22 62362043

 

For General Queries & Technical Support

contact@quantumphinance.com

 

For Sale and Demos

sales@quantumphinance.com