Dynamically Plot Data Points in Scatterplot

[merlinCLT]

Hi All!

Happy Tuesday! Thanks, in advance, for any support you can provide. The data below are all fictitious.

I have been working on dynamically plotting data points in an Excel chart (scatterplot) based on specific conditions within my dataset. Specifically, my goal is to display data points only for rows where a certain condition is met in one column (CU), which contains TRUE or FALSE values. I want to plot the corresponding data points from another column (Series Name from column B; X value from column CW; Y value from CX) in the order of their occurrences of TRUE values.

To achieve this, I am using formulas that I have incorporated into the Name Manager that dynamically checks for TRUE values in column CU, counts their occurrences, and retrieves the corresponding data points from the aforementioned columns for plotting. For instance, if the 1st occurrence of TRUE is in row 3, and the 2nd occurrence is in row 5, I want the data from columns for these rows to be plotted in the chart in the same order.

Formula examples:
Series Name 1:

=IF(COUNTIF(Data_Table!$CU$3:$CU$500, TRUE) = 0, NA(), INDEX(Data_Table!$C$3:$C$500, MATCH(TRUE, Data_Table!$CU$3:$CU$500, 0)))

X Value 1:

=IF(COUNTIF(Data_Table!$CU$3:$CU$500, TRUE) >= 1, INDEX(Data_Table!$CW$3:$CW$500, MATCH(TRUE, Data_Table!$CU$3:$CU$500, 0)), "")

Y Value 1:

=IF(COUNTIF(Data_Table!$CU$3:$CU$500, TRUE) >= 1, INDEX(Data_Table!$CX$3:$CX$500, MATCH(TRUE, Data_Table!$CU$3:$CU$500, 0)), "")

I am using checkbox linked to the cells in CU to indicate whether the value in CU cells should be TRUE or FALSE.

Additionally, I need to handle cases where there are fewer occurrences of TRUE than the desired threshold. For example, if I'm looking for the 20th occurrence of TRUE, but there are only 15, the chart should not display any erroneous or placeholder values such as '0'. I have attempted to mitigate this issue by using advanced Excel functions like INDEX, SMALL, IF, and IFERROR to manage the data dynamically and ensure accurate plotting. However, if I leave the Data Series active in the 'Select Data Source' and deselect a participant from the participants menu the data point remains plotted at 0,0 in the chart.

This seems to only happen when I use the named ranges, as opposed to "hard coding" the cell references. That is, when I uncheck the checkboxes next to participant names, their data is not reflect in the chart, even if their data series is still active/checked in the 'Select Data' window.

For example, the chart below is what is produced when I edit the series as:

Series Name: =Data_Table!$C$3
Series X Values: =Data_Table!$CW$3
Series Y Values: =Data_Table!$CX$3

Is there a way that I can have the same result using named ranges/formulas in the Name Manager? Any support/guidance is appreciated.

Thanks,

MerlinCLT

 

[felixstraube]

Hi, it is not clear to me why you are counting occurrences of TRUE.

The only way i found to not plot no data is using NA().
But you can not use it in the named range directly.
I used some helper columns DB:DD with these formulas:

Book9.xlsx
	A	B	C																																																																																															CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE
1
2		Serie name																																																																																																	Plot?		X	Y				Name 1	1	1.1
3		Name 1	Name 1																																																																																																TRUE		1	1.1				Name 2	2	2.2
4		Name 2	Name 2																																																																																																TRUE		2	2.2				Name 3	3	3.3
5		Name 3	Name 3																																																																																																TRUE		3	3.3
6		Name 4	Name 4																																																																																																		4	4.4
7
Sheet1

Cell Formulas
Range	Formula
DB2:DB4	DB2	=IFERROR(LET( plot, Sheet1!$CU$3:$CU$1001, name, Sheet1!$B$3:$B$1001, FILTER(name, plot) ), "noName")
DC2:DC4	DC2	=IFERROR(LET( plot, Sheet1!$CU$3:$CU$1001, x, Sheet1!$CW$3:$CW$1001, FILTER(x, plot) ), 0)
DD2:DD4	DD2	=IFERROR(LET( plot, Sheet1!$CU$3:$CU$1001, y, Sheet1!$CX$3:$CX$1001, FILTER(y, plot) ), NA())
Dynamic array formulas.

And named ranges:
Range name: "name"
Formula:

=OFFSET(Sheet1!$DB$2, 0, 0, COUNTA(Sheet1!$DB$2:$DB$1000))

Range name: "x",
Formula:

=OFFSET(Sheet1!$DC$2, 0, 0, COUNTA(Sheet1!$DB$2:$DB$1000))

Range name: "y",
Formula:

=OFFSET(Sheet1!$DD$2, 0, 0, COUNTA(Sheet1!$DB$2:$DB$1000))

And use these named ranges in the plot:

So if you have data to plot you get the point, and if you don't no point is plotted:

Although I don't know if I understood you correctly. Let me know

 

[merlinCLT]

Thank you so much for the detailed response and proposed

Hi, it is not clear to me why you are counting occurrences of TRUE.

The only way i found to not plot no data is using NA().
But you can not use it in the named range directly.
I used some helper columns DB:DD with these formulas:

Book9.xlsx
	A	B	C																																																																																															CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE
1
2		Serie name																																																																																																	Plot?		X	Y				Name 1	1	1.1
3		Name 1	Name 1																																																																																																TRUE		1	1.1				Name 2	2	2.2
4		Name 2	Name 2																																																																																																TRUE		2	2.2				Name 3	3	3.3
5		Name 3	Name 3																																																																																																TRUE		3	3.3
6		Name 4	Name 4																																																																																																		4	4.4
7
Sheet1

Cell Formulas
Range	Formula
DB2:DB4	DB2	=IFERROR(LET( plot, Sheet1!$CU$3:$CU$1001, name, Sheet1!$B$3:$B$1001, FILTER(name, plot) ), "noName")
DC2:DC4	DC2	=IFERROR(LET( plot, Sheet1!$CU$3:$CU$1001, x, Sheet1!$CW$3:$CW$1001, FILTER(x, plot) ), 0)
DD2:DD4	DD2	=IFERROR(LET( plot, Sheet1!$CU$3:$CU$1001, y, Sheet1!$CX$3:$CX$1001, FILTER(y, plot) ), NA())
Dynamic array formulas.

And named ranges:
Range name: "name"
Formula:

=OFFSET(Sheet1!$DB$2, 0, 0, COUNTA(Sheet1!$DB$2:$DB$1000))

Range name: "x",
Formula:

=OFFSET(Sheet1!$DC$2, 0, 0, COUNTA(Sheet1!$DB$2:$DB$1000))

Range name: "y",
Formula:

=OFFSET(Sheet1!$DD$2, 0, 0, COUNTA(Sheet1!$DB$2:$DB$1000))

And use these named ranges in the plot:

View attachment 115037

So if you have data to plot you get the point, and if you don't no point is plotted:

View attachment 115038

Although I don't know if I understood you correctly. Let me know

Thank you so much for your detailed response and shared solution. Your proposal works very well with the exception of one issue I am encountering:

Issue: The labels for the x and y series associated appropriately with the data labels; however, when I turn on the data label for the series name all names are listed for each data point.

Do you have any thoughts on how to resolve this?

Thank you, again!

 

[felixstraube]

Thanks for the feedback. Happy to help.
Try this:

Where you can replace Book9.xlsx with your book name.
Let me know if this resolves your issue.

 

[merlinCLT]

Thanks for the feedback. Happy to help.
Try this:

View attachment 115071

Where you can replace Book9.xlsx with your book name.
Let me know if this resolves your issue.

Thanks, again, Felix! That worked. You were such a huge help!

 

[felixstraube]

Thanks for the feedback. I'm glad I could help.