Avoiding repeat of IF statement without helper cell

[Tom83]

Hi guys,

I have a graph line that shows zero's for future quarters, I want to remove them for the green line. Formatting the cell in any way doesn't fix the issue, it's sees the cell as a value zero.

The only way is to make the future values #N/A, that fixes the issue. But the formula is very long and I would like to avoid repeating the IF statement, is there any way I can let the formula evaluate itself without repeating the IF statement and without using a helper cell?

 

[RoryA]

It might be easier to use a dynamic named range for the line series' data range that only goes up to a specified period (or just finds the last non-zero value).

 

[Tom83]

It might be easier to use a dynamic named range for the line series' data range that only goes up to a specified period (or just finds the last non-zero value).

Thanks for this, sounds like a good option. As I'm not that experienced, how would I do this?

 

[RoryA]

You'd use a combination of INDEX and MATCH generally. Is there a cell with the current quarter value in it that can be used for the match? We also need to know which ranges the values and category labels are in.

 

[Tom83]

You'd use a combination of INDEX and MATCH generally. Is there a cell with the current quarter value in it that can be used for the match? We also need to know which ranges the values and category labels are in.

Great, yes there is:

I extract the values in cells F7:I10 referring to a pivot table

 

[Tom83]

You'd use a combination of INDEX and MATCH generally. Is there a cell with the current quarter value in it that can be used for the match? We also need to know which ranges the values and category labels are in.

Hi RoryA, any idea how to fix this?

 

[RoryA]

Your category range would be:

=Sheet1!$B$6:INDEX(Sheet1!$6:$6,MATCH("Q1-21",Sheet1!$6:$6,0))

for example, and you'd repeat a similar construction for the data series. Ideally you'd have Q1-21 in a cell somewhere so that you can reference that cell and then update all the formulas just by updating that cell.

 

[Tom83]

Your category range would be:

=Sheet1!$B$6:INDEX(Sheet1!$6:$6,MATCH("Q1-21",Sheet1!$6:$6,0))

for example, and you'd repeat a similar construction for the data series. Ideally you'd have Q1-21 in a cell somewhere so that you can reference that cell and then update all the formulas just by updating that cell.

Great, almost there! I just get an error when putting this in the category axis:

=Sheet1!$B$6:INDEX(Sheet1!$6:$6,MATCH("Q1-21",Sheet1!$6:$6,0))

 

[RoryA]

Sorry - I missed out an important step! You should use those formulas in defined named ranges (using the Name Manager), then use the name in the axis label dialog. For example define a name called AxisLabels that uses the formula above (assuming sheet name is correct), then in that dialog you'd use: =Sheet1!AxisLabels

 

[Tom83]

Sorry - I missed out an important step! You should use those formulas in defined named ranges (using the Name Manager), then use the name in the axis label dialog. For example define a name called AxisLabels that uses the formula above (assuming sheet name is correct), then in that dialog you'd use: =Sheet1!AxisLabels

Thanks, almost there! What to put in the series values dialog? As can be seen in the graph the values are still showing zero's, the axis is correct.