0

I have a table in a Google sheet that looks like this:

The data updates daily with a new column added after Column "Date" and is intended to track how quickly my team moves through projects. I am wanting to chart the data as a scatterplot so that I can add Linear Trendlines for each series. However, these lines are affected by all of the 0 values. I would like them to start with the last 0 value that precedes the first non-zero data point. The easiest solution I can see would be to use a macro to clear 0 values from the table. I am struggling with how to

  • A) run the trendline through 0 in google sheets as there isn't really an option I can see; or
  • B) delete all 0 values except for where the cell to the left is greater than 0.

This is currently where I am at with Row C.

But would like it to look like the following:

With the table potentially looking like this (Note highlighted the row with a zero preceding a value >0):

2
  • You can use slicers, but that will work like a filter that will remove these values from the spreadsheet too when you have it enabled. Another solution is to have a separate column with a formula such as =IF(A1=0%,"",A1), so the 0.00% values will be excluded when building the chart. You can also just remove these values from the spreadsheet (or have a text that says something like "No Data"), assuming this won't affect your use case!
    – Zo-Bro-23
    Feb 1, 2023 at 4:12
  • Use a sepperate table for your chart that uses the original table as it's source but transforms it as you want. Please consider putting some data in a markdown table or sharing a link to some sample data in case you want anyone to actually manipulate anything and not have to generate the source datas first.
    – Blindspots
    Feb 1, 2023 at 6:41

1 Answer 1

2

As Blind Spots suggests in their comment, you can Insert > Sheet and use a formula in cell A1 of the new sheet to copy the data from the current sheet, omitting trailing zeros, like this:

=map( 
  Sheet1!A1:ZZ, 
  lambda( 
    cell, 
    if( 
      (cell = 0) * iferror(offset(cell, 0, -1) = 0), 
      iferror(1/0), 
      cell 
    ) 
  ) 
)

The iferror(1/0) pattern will produce a truly blank value, which is different from a zero-length text string "".

Then set the data range of your chart so that it points to the new sheet.

2
  • Nice doubleunary. +10
    – Blindspots
    Feb 1, 2023 at 8:00
  • Edited the answer to use correct offset and map() instead of arrayformula(). Feb 1, 2023 at 10:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.