Showing Continuous Trend even with Gap Data

eLIZo

For context, I am trying to show month by month trend, but there are some months where there are no data.

Right now, my trend column (#3) compares the data(Column #1) in the current row to the row right below it. For rows where the row below has no data, is there a formula to get it to skip the empty row and compare it to the next row with data?

Paul Newcome

NOT(@cell = "Something")

and

@cell <> "Something"

are interchangeable depending on user preference. I tend to avoid the NOT function when I can because it is just one more set of parenthesis cluttering things up.

The second Row column is more out of habit for me because it will maintain that numerical order from top to bottom regardless of sorting and is not affected by adding or deleting new rows anywhere in the sheet. Using just the auto-number column, those numbers are locked to that row, so they can get pretty out of order if you end up sorting the sheet. It will also mean there could be a gap or gaps in numbers if rows were deleted, and they could also be out of order if new rows are added anywhere other than the same place (always at the top or always at the bottom).

I am very frequently adjusting and tweaking, so I tend to use the auto-number as well as the text/number to ensure I always have that row number no matter what since it is typically one of the first things I build into any sheet.

Paul Newcome

Are you able to provide a screenshot of more of the sheet for context to include a row or set of rows that has no data?

eLIZo

The row that states "No batches" is a row with no data. It is a formula based column that returns "No Batches" if there is no data"

So my goal is for the row with 75%, the arrow would be a down arrow since it would be compared against the 100%, instead of the current "No Batches" row

Paul Newcome

Ah. Ok. In that case... Insert an auto-number type column (called "Auto" in this example and formatting doesn't matter) and a text/number type column (called "Row" in this example).

In the Row column, use this column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Then to get the next row down that is not "0 Batches.......", you would use:

=IFERROR(INDEX(COLLECT([% Column name]:[% Column Name], [% Column name]:[% Column Name], @cell <> "0 Batches.......", ROw:Row, @cell> Row@row), 1), "")

From there you would drop that whole thing into your (I am assuming) IF statement that outputs the arrows.

=IF([% Column name]@row> IFERROR(INDEX(COLLECT(...............), "Up", IF([% Column name]@row< IFERROR(INDEX(COLLECT(...............), "Down", "No Change"))

eLIZo

Hi Paul, thank you for the help so far. I tried the formula, but the INDEX(COLLECT( part of the formula keeps returning a "INVALID VALUE" error code, but I'm sure what is going on.

[% Column name]:[% Column Name], [% Column name]:[% Column Name], @cell <> "0 Batches.......", ROw:Row, @cell> Row@row), 1)

Paul Newcome

That means it is not finding any rows that meet the range/criteria sets. Are you able to provide a screenshot with the updates including the new columns that were added?

eLIZo

Hi Paul, I was able to figure out the rest.

Instead of using <>, I replaced it with a NOT() formula. Additionally, I only created one new auto-number type column. The second text/number column wasn't needed.

The full final formula came to :

=IF([... Release %]@row = "0 Batches Expected", "---", IF([... Release %]@row > IFERROR(INDEX(COLLECT([... Release %]:[... Release %], [... Release %]:[... Release %], NOT(@cell = "0 Batches Expected"), [Row ID]:[Row ID], @cell > [Row ID]@row), 1), ""), "Up", IF([... Release %]@row < IFERROR(INDEX(COLLECT([... Release %]:[... Release %], [... Release %]:[... Release %], NOT(@cell = "0 Batches Expected"), [Row ID]:[Row ID], @cell > [Row ID]@row), 1), ""), "Down", "---")))

Paul Newcome

NOT(@cell = "Something")

and

@cell <> "Something"

are interchangeable depending on user preference. I tend to avoid the NOT function when I can because it is just one more set of parenthesis cluttering things up.

The second Row column is more out of habit for me because it will maintain that numerical order from top to bottom regardless of sorting and is not affected by adding or deleting new rows anywhere in the sheet. Using just the auto-number column, those numbers are locked to that row, so they can get pretty out of order if you end up sorting the sheet. It will also mean there could be a gap or gaps in numbers if rows were deleted, and they could also be out of order if new rows are added anywhere other than the same place (always at the top or always at the bottom).

I am very frequently adjusting and tweaking, so I tend to use the auto-number as well as the text/number to ensure I always have that row number no matter what since it is typically one of the first things I build into any sheet.

eLIZo

Hi Paul,

Thank you for explaining further. That makes a lot of sense.

Paul Newcome

Happy to help. 👍️