Home > database >  Avoid Excel Chart streching line till end after series flattens
Avoid Excel Chart streching line till end after series flattens

Time:12-13

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

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.

CodePudding user response:

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 enter image description here

Added benefit: if overlap is set to 100% on the blue series then it looks like the value is "highlighted" in the first series - when discussing data it is a neat way to focus attention.

Edit, this works whether the preceeding values increase or decrease: enter image description here

CodePudding user response:

So to solve my problem, I created one more table and applied the formula =IF(MAX(B$1:B$8)=H1,NA(),B2). This formula computes the maximum value of the source table column and compares it with the upper immediate value. New table looks like this-

New Table

Range of this table is from G1:K8

CodePudding user response:

1-Select a column (or columns) to look for duplicated data.

2-Open the Data tab at the top of the ribbon.

3-Find the Data Tools menu, and click Remove Duplicates.

4-Press the OK button

Is this that you want?

  • Related