1

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.

DecimalTurn
  • 3,243
  • 3
  • 16
  • 36
shyamsantoki
  • 115
  • 1
  • 9

4 Answers4

1

Replace the zeroes.

Use na()

As that is not plotted.

See: 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

Solar Mike
  • 7,156
  • 4
  • 17
  • 32
1

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.

DecimalTurn
  • 3,243
  • 3
  • 16
  • 36
  • So what happens if values decrease? Many data that are plotted both increase and decrease over time. So how will your answer need to be improved? – Solar Mike Dec 12 '21 at 09:35
  • @SolarMike It actually works when the data decreases, it just doesn't if the data stays the same before the end is reached. I didn't think I'd have to mention decreasing since all charts and data in the question never show any decrease, but I've edited my answer to clarify. – DecimalTurn Dec 12 '21 at 09:48
  • I have solved this problem. The best solution would look like this- first of all, find maximum value from the raw table for each columns, and then use a formula ```=IF(N(B1)=MAX(H$0:H0),NA(),B1)```. – shyamsantoki Dec 12 '21 at 13:05
  • @shyamsantoki Thanks for providing your solution! I still think my answer could be useful to solve a more general case of the problem you have, so I've edited the answer account for that. – DecimalTurn Dec 12 '21 at 21:00
1

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

shyamsantoki
  • 115
  • 1
  • 9
  • I guess if it's impossible for a series in your dataset to reach its maximum, then go down before going back up again; this is way more elegant! – DecimalTurn Dec 12 '21 at 20:48
0

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?

Gürkan Özdem
  • 109
  • 7
  • Unfortunately, I cannot use this approach because there are some duplicates in a column before it reaches the maximum value. – shyamsantoki Dec 12 '21 at 08:45