Using formulas in Select Data > Edit > Series Y Values

[DashboardNovice]

In the first chart, I can plot a vertical line by hard coding the Y values by doing Select Data > Edit > Series Y Values and using ={0,3500}

However instead of hard coding the Y values, I would like to use formulas to get the MIN( ) and the MAX( ) values from column C. I tried =MIN(Sheet1!$C$5:$C$105),max(Sheet1!$C$5:$C$105) and {=MIN(Sheet1!$C$5:$C$105),max(Sheet1!$C$5:$C$105)} and neither of these worked.

Is it possible to use formulas in the Edit Series window, is it possible to use formulas in the field called Series Y values?

 

[Peter Bartholomew]

You cannot use a formula within the SERIES function, but you can use a defined name. I prefer a local worksheet-scoped name such as Sheet1!X. Ways of creating an array from single cell references or formulas include

X
= IF({1,0}, ref, ref);
Y
= CHOOSE({1,2}, formula1, formula2);
Y
= HSTACK(formula1, formula2);

The SERIES function then references the names Sheet1!X and Sheet1!Y.

 

[DashboardNovice]

You cannot use a formula within the SERIES function, but you can use a defined name. I prefer a local worksheet-scoped name such as Sheet1!X. Ways of creating an array from single cell references or formulas include

X
= IF({1,0}, ref, ref);
Y
= CHOOSE({1,2}, formula1, formula2);
Y
= HSTACK(formula1, formula2);

The SERIES function then references the names Sheet1!X and Sheet1!Y.

Maybe I'm misunderstanding, but I hoping to enter a formula in the field called Series Y values after you right click > Select Data > Edit

I have a picture attached. Is it possible to use formulas in this field?

 

[Peter Bartholomew]

Sadly, I do not think there is any misunderstanding. I realise what you are trying to do, and it is not possible; the SERIES function does not accept formulas. It will accept ranges (directly referenced or by name) and arrays (array constants or named arrays). It is that final option that allows one to chart data that does not even appear within the worksheet.

 

[DashboardNovice]

Sadly, I do not think there is any misunderstanding. I realise what you are trying to do, and it is not possible; the SERIES function does not accept formulas. It will accept ranges (directly referenced or by name) and arrays (array constants or named arrays). It is that final option that allows one to chart data that does not even appear within the worksheet.

1. It sounds like when you enter values in that Edit Series window, those values automatically get fed to the SERIES ( ). Is that true? If so, where is that SERIES ( ) located?

2. Let's say I created named ranges using the 3 examples you gave of X, Y and Y. If I understand what you're saying, I would then use the SERIES( ) and drop in those named ranges. Where would you actually enter the SERIES( )?

3. When I choose a blank cell, type = and start typing "SERIES" the only function that appears is SERIESSUM( ). I don't see the SERIES( ) function as an option. Why not?

 

[Peter Bartholomew]

I suspect that the chart engine is both old and somewhat distinct from the regular calc engine of Excel. To see the relevant SERIES function, one first creates a chart with some default series. Selecting the series on the chart brings up the associated SERIES on the formula bar. It can be edited there, but is incredibly picky (eg no embedded spaces) and offers no help when it comes to correcting errors in the formula (simply refuses to accept the input). Defined names need to be fully-qualified by the workbook, or sheet name, as appropriate. The defined names you use will normally refer to worksheet ranges but that is not essential, an array formula would also work.

 

[DashboardNovice]

You cannot use a formula within the SERIES function, but you can use a defined name. I prefer a local worksheet-scoped name such as Sheet1!X. Ways of creating an array from single cell references or formulas include

X
= IF({1,0}, ref, ref);
Y
= CHOOSE({1,2}, formula1, formula2);
Y
= HSTACK(formula1, formula2);

The SERIES function then references the names Sheet1!X and Sheet1!Y.

In the examples above, what does X, Y and Y refer to? Where would you physically enter those? Are these the arguments that go in the SERIES( )? That function takes 4 arguments.

 

[Peter Bartholomew]

The series formula I used was

=SERIES("Call Strike",Cht!U,Cht!V,2)

with arguments {name, x-coords, y-coords, seriesNum} where Cht!V is defined using Name Manager

 

[DashboardNovice]

The series formula I used was

=SERIES("Call Strike",Cht!U,Cht!V,2)

with arguments {name, x-coords, y-coords, seriesNum} where Cht!V is defined using Name Manager
View attachment 81158View attachment 81158

Interesting. I think I see what you're doing, but when I opened the workbook attached in your reply, the Name Manager is blank.

Normally when you use the CHOOSE( ) you return only one value, but since you're using an array, you can return more than one value (2 values in this case). So ultimately you are using an array to deliver 2 values for the X coordinates and 2 values for the Y coordinates. That's pretty cool.

 

[Peter Bartholomew]

That is correct. IF({1,0}, ..., ...) and HSTACK(..., ...) are other ways of building values into an array. Second go lucky with the file??