Avoiding repeat of IF statement without helper cell

Tom83

New Member
Joined
Apr 23, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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.

1619165688337.png


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?
 
You'd create a similar name for each series, adjusting the relevant part of the formula (not the blue part as you always want to find the Quarter info in row 6):

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

Ideally, you could create a separate name (called say MatchCol) using

Excel Formula:
=MATCH("Q1-21",Sheet1!$6:$6,0)

then your series named formulas would look like:

Excel Formula:
=Sheet1!$B$9:INDEX(Sheet1!$9:$9,MatchCol)
and
Excel Formula:
=Sheet1!$B$10:INDEX(Sheet1!$10:$10,MatchCol)
and so on
 
Upvote 0
Solution

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
You'd create a similar name for each series, adjusting the relevant part of the formula (not the blue part as you always want to find the Quarter info in row 6):

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

Ideally, you could create a separate name (called say MatchCol) using

Excel Formula:
=MATCH("Q1-21",Sheet1!$6:$6,0)

then your series named formulas would look like:

Excel Formula:
=Sheet1!$B$9:INDEX(Sheet1!$9:$9,MatchCol)
and
Excel Formula:
=Sheet1!$B$10:INDEX(Sheet1!$10:$10,MatchCol)
and so on
Great, it works! I created a named cell called Quarter to define the value as a parameter to update. However I still need row 20 as helper cells to use in the series:

1619686678914.png


How can I avoid this?
 
Upvote 0
Why do you need row 20? How did you define the SeriesValues name - it should be using the formulas we discussed in its RefersTo source in Name Manager.
 
Upvote 0
Why do you need row 20? How did you define the SeriesValues name - it should be using the formulas we discussed in its RefersTo source in Name Manager.
This is what I have now:

test graph.xlsx
ABCDEFGHIJKLMNOPQRS
2Q2-21
3
4Revenue & EBIT (EURm)
5
6
7Q1-20Q2-20Q3-20Q4-20Q1-21Q2-21Q3-21Q4-21
8Revenue0,00,00,00,00,00,00,00,0
9Revenue Budg.0,00,00,00,0
10EBIT-2,8-2,7-2,4-3,3-3,2-3,2-2,70,0
11EBIT Budg.-3,5-3,6-3,6-3,7
12
13 Blank zero
14
15
16
17Index formula column:Q1-20Q2-20Q3-20Q4-20Q1-21Q2-21
18
19
20Index formula series-2,8-2,7-2,4-3,3-3,2-3,2Named cells $B$20:$I$20 as SeriesValues
21
22MatchCol formula:7
23
24
25
26
Sheet1
Cell Formulas
RangeFormula
B17:G17B17=$B$7:INDEX(Sheet1!$7:$7,MATCH(Quarter,Sheet1!$7:$7,0))
B20:G20B20=$B$10:INDEX(Sheet1!$10:$10,MATCH(Quarter,Sheet1!$7:$7,0))
B22B22=MATCH(Quarter,Sheet1!$7:$7,0)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
Quarter=Sheet1!$A$2B22, B20, B17
 
Upvote 0
In the RefersTo box for the SeriesValues, you should have the formula
Excel Formula:
=$B$10:INDEX(Sheet1!$10:$10,MATCH(Quarter,Sheet1!$7:$7,0))
. And a similar set up for the AxisLabels and MatchCol names (i.e. the formulas go directly in the RefersTo box). Then you won't need rows 17, 20 and 22.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top