Formula to Replace cell value having 0 with preceding non zero value

[Mahesh6279]

Please help me with a formula to replace a 0 value cell with a non-zero value from preceding non-zero cell value or from the next cell. End result should be all cells having a non-zero value even if one cell has a non-zero value. If all cells are zero then ignore it and keep it zero

Appreciate your help thanks

[ShankerV]

Hi @Mahesh6279 

This can be done without the help of Macros.

Below are the steps.

Step 1: Input

Step 2: Use the Transpose tool 

Step 3: Use Tile tool to do the numbering

Step 4: Use Sort tool to sort by descending based on the above Tile tool results

Step 5: Use Multi-Row formula to overwrite 0 with previous cell value.

Step 6: Use Sort tool to sort by ascending

Step 7: Use Crosstab tool to get your desired output.

Many thanks

Shanker V

[FinnCharlton]

Hi @Mahesh6279 , you can do this with the multi-row formula tool - you just have to transpose your data first. Hope this helps!

[Mahesh6279]

@FinnCharlton this is not working if the first month is 0

[binuacs · Accepted solution]

@Mahesh6279 One way of doing this

[Mahesh6279]

@binuacs thanks a lot sir. this is working fine

[Mahesh6279]

@binuacs i encountered a problem. after applying the formula the numbers from the above component 1 is getting filled for component 2

[binuacs]

@Mahesh6279 Make sure that the group by option is selected in the multi-row tool