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

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

Datasets

Macros

Help

Expression

Iterative Macro

Accepted answers

@Mahesh6279 One way of doing this

Transpose_310723.yxmd

All comments

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

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

Screenshot 2023-07-31 113707.png

Replace with non-zero value.yxmd

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

@Mahesh6279 One way of doing this

Transpose_310723.yxmd

@binu_acs thanks a lot sir. this is working fine

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

Quick Links

Tags

This months top contributors

atcodedog05 19598

Qiu 15867

MarqueeCrew 13708

apathetichell 13703