Search code examples
excelexcel-formulaexcel-2016excel-charts

Avoid Excel Chart streching line till end after series flattens

I have a table of total 12 columns and 30 rows. The table looks like below. Note that real data are very different than this, but follows this pattern - the value goes upto some number and keeps repeating for all rows.

Tabele

Data

I want to plot a line chart that looks like this-

Expected output

But I am getting this-

Actual output

I am able to get an expected chart by manually deleting repeating values from the table. I am looking for a way to do that automatically.

Solution

  • Assuming your data is arranged like this: enter image description here

    You could create another table referring to the first one with these formulas:

    enter image description here

    So basically, this formula =IF(B3-B2=0,NA(),B3) in H4 copy-pasted in all cells but the first row.

    Which would give:

    enter image description here

    And plotting this second table would give you the desired result since NAs aren't plotted (as mentionned by Solar Mike).

    Caveat

    This works only if the values are stricly increasing or decreasing for every row. If there is no change between 2 data points before the end of the series where it flattens for good, then there would be missing point in your line.

    For example, if 2020.Q2 started with two zeroes in a row, you would have a NA appearing before you want it.

    enter image description here

    So, you would still need to manually replace those NAs.

    But if you want to automate the whole process, you could add another table that checks if there is non-NA values after a NA and if there is change it back to the previous number.

    Something like this:

    enter image description here

    In this solution, the formula in O3 would have to be : =IF(AND(ISNA(I3),PRODUCT(IF(NOT(ISNA(I4:I$8)),0,1))=0),I2,I3)

    Explanation:

    • I4:I$8 is the range of values after the current cell. We use the $ so that the range is anchored to the last row.
    • IF(NOT(ISNA(I4:I$8)),0,1) returns an array filled with 0's where there is a non-NA value and 1's when the value is NA.
    • PRODUCT(IF(NOT(ISNA(I4:I$8)),0,1))=0 checks if the product of the elements in the array is 0. Since only one zero is needed for the value of the product to be zero, this essentially checks if there's at least one non-NA value after the current one.

    EDIT: If it's impossible for a series in your dataset to reach its maximum before the end, then the solution you found is way simpler. However, the method I'm suggesting is more general since it works whether the series flattens at its maximum, minimum or anywhere in between.