If statement in chart

[Romano]

Hi,
Is it possible for a chart to plot a different series (column) depending on the result of an equation? The situation is something like this:

If cell A1 = 10 then plot column C
If cell A1 = 20 then plot column D

Thanks

[Bryan Hessey]

Hi,
Is it possible for a chart to plot a different series (column) depending on the result of an equation? The situation is something like this:

If cell A1 = 10 then plot column C
If cell A1 = 20 then plot column D

Thanks

Hi,

yes, plot column E with If(A1=10,C1,D1)

hth
---

[SteveG]

romano,

Name your ranges to plot (I used A and B for this example). Create another named range (I used ChtVals) and enter this formula in the Refers to: box.

=CHOOSE(A1/10,A,B)

When you create your chart for the series values enter the sheet name and the ChtVals so Sheet1!ChtVals.

When the number in A1 is 10 it plots the first named range or A. When you change to 20 it plots the second named range or B. You can add additional named ranges to the CHOOSE function separated by commas up to 29 ranges. The A1/10 creates an index number so the CHOOSE selects the correct named range. 1 being A, 2 being B and so on as you add named ranges separated by commas. This formula is dependent upon the value in A1 being a multiple of 10.
HTH

Steve

[Romano]

I'm not too familiar with the Choose function, but it sounds similar to using nested IF statements in a column created just for creating the chart. This method does work since the chart always refers to the same column and the contents of that column change, but you do have to have a special column just for the chart. Good idea. I might try that.

Or maybe I misunderstood your post. Are you saying that the Choose statement is actually the Y data for the chart?

Thanks for your help

David

[SteveG]

Romano,

The CHOOSE function is actually input as a named range and not in the spreadsheet itself. Go to Insert>Name>Define. Name the function (I used ChtVals) for chart values. In the Refers to box enter the CHOOSE function.

=CHOOSE(A1/10,A,B)

Click OK.

A and B represent the named ranges for your chart's Y values. To name those select the range, go to Insert>Name>Define and enter in a name (A). You'll see in the refers to box that because you selected your range first it is already populated there. Click OK. Do the same for range B.

Now when you set up the chart enter in the series values for the chart as Sheet1!ChtVals not a reference to a specific column.

Hope that clarifys it for you.

Steve

[Romano]

SteveG,
Ah, I see what you mean, that is a good way to do it. Thanks for the tip!

David

[SteveG]

Glad I could help.

Cheers,

Steve