CODEWITHSUNDEEP
excelexcel-2010
user5155835
user5155835

Reputation: 4742

Excel - Line Chart Ignore #N/A Series Value

I have data such as follows:

enter image description here

The date extends till 14-02-2024

The Cost column contents are derived using:

=IF($Q2=TODAY(),$I$2,NA())

I have following formula:

=OFFSET(Funds!$R$2,0,0,COUNTA(Funds!$R:$R)-1)

And I use this formula as a Series in the Line Chart. And the Horizontal Axis is:

=Funds!$Q$2:$Q$1721

Now, the line chart looks like this:

enter image description here

This is because only the initial dates have values, whereas the future dates have #N/A How can these future dates with #N/A values be ignored?

I want the chart to display the values from past to only current date, not after today's date.

Upvotes: 1

Views: 1298

Answers (2)

Sapan Gupta
Sapan Gupta

Reputation: 108

use iferror(offsetformula,0) it will replace N/A to 0 and your chart will be drawn if you want to keep those entries. Please reply if you want your rows to be completely removed if N/A value.

Upvotes: 0

user5155835
user5155835

Reputation: 4742

The solution was to have #N/A in Date column for future dates.

I added an extra column (P) with all dates, past and future.

Then, I added to the date column:

=IF($P2<=TODAY(),$P2,NA())

So only dates till today will have date values and all future dates will have #N/A

enter image description here

The Line Chart will not display data for #N/A dates.

Upvotes: 1

Related Questions