Can a chart range have an IF statement?

[birdieman]

In excel, I would like to make a chart whose range depends on an if statement. So, can you put an if statement in the "series values" when making a chart?? For example, if cell B6 is 50 or less, make a chart whose series values is C6:C30, otherwise the series values is C6:C20?


I tried a formula in the "series values" box, but kept getting an "invalid function" and I do not know if you cannot do what I want, or if I have a bad syntax formula.
thanks for looking

 

[sheetspread]

Maybe a chart off a named range with the formula?

 

[sheetspread]

Here's an example:

https://vimeo.com/184695227

 

[birdieman]

will explore that option, but I would still need an if statement, and I do not know if an "if" statement is allowed when specifying a range -- whether the range is named or not?????

thanks for responding

 

[sheetspread]

It is

 

[sheetspread]

Try:

=OFFSET('Sheet6 (2)'!$C$6,,,IF('Sheet6 (2)'!$B$6>50,15,25))

(change to your sheet name)

 

[birdieman]

In your example, what is the (2) after the sheet name? or what does it stand for? thanks

 

[birdieman]

=offset('annual recap (2)' !$K$6,,,If('Annual recap (2)'!$b$6<50,70,40))

The above line, which I put in Series values, gives me an "invalid function" error

the sheet name where the data is located (not the sheet were the chart resides) is Annual Recap; the data to chart begins in K6, and I want to plot the first 70 values of 80 total values (in column K) if B6 < 50 and plot 40 values of 80 total values in same column if not.

Did I do something wrong

 

[sheetspread]

I copied Sheet6 without renaming so Excel calls it Sheet6(2). Don't add a (2) if it's not part of the name.

 

[CatOnASpreadsheet]

You can create a new Named Range (Formulas > Name Manager > New), give it a name and add the following formula:

=IF('Annual Recap'!$B$6<50,'Annual Recap'!$K$4:$K$45,'Annual Recap'!$K$6:$K$85)

Then create your chart, right click on it > Select Data, Add Series, name it as you wish and in the Series values delete everything and add

='Annual Recap'!thenameofyourrange

I hope this helps.

Cheers,
Catalin