How to loop through data to generate charts

[gshock]

How do I loop through data to create a chart with multiple Series. Then, how to I repeat that to create multiple charts, using that same methodology for the rest of the data?

Below is a table of fictional data, but the order of indexing is real. That is, at a given speed, suction pressure is held constant, and discharge pressure is increased. A program calculates the required HP. The suction pressure is increased, speed is still constant, and the discharge pressure is incremented, and HP is calculated.

Then the speed is increased to the next interval, and the process is repeated. I'm trying to figure out a way to loop through the day and create a chart for each speed range. In reality, there are too many data points to create all of the series on one chart. So I want to create the charts based on speed. I'm struggling with how to lay out the format for this. Should i used nested FOR loops? Should I use a Case structure? What's the best way to go about it? I haven't learned enough about Chart objects yet to understand how to populate a series using VBA.

I'm not looking for someone to do this for me. I really want to learn, and I'm only asking for guidance. Thank you in advance.

speed	Psuction	Pdischarge	Power
100	100	500	750.00
100	100	505	756.25
100	100	510	762.50
100	100	515	768.75
100	100	520	775.00
100	100	525	781.25
100	100	530	787.50
100	100	535	793.75
100	100	540	800.00
100	100	545	806.25
100	100	550	812.50
100	105	500	750.00
100	105	505	756.25
100	105	510	762.50
100	105	515	768.75
100	105	520	775.00
100	105	525	781.25
100	105	530	787.50
100	105	535	793.75
100	105	540	800.00
100	105	545	806.25
100	105	550	812.50
100	110	500	750.00
100	110	505	756.25
100	110	510	762.50
100	110	515	768.75
100	110	520	775.00
100	110	525	781.25
100	110	530	787.50
100	110	535	793.75
100	110	540	800.00
100	110	545	806.25
100	110	550	812.50
200	100	500	875.00
200	100	505	881.25
200	100	510	887.50
200	100	515	893.75
200	100	520	900.00
200	100	525	906.25
200	100	530	912.50
200	100	535	918.75
200	100	540	925.00
200	100	545	931.25
200	100	550	937.50
200	105	500	875.00
200	105	505	881.25
200	105	510	887.50
200	105	515	893.75
200	105	520	900.00
200	105	525	906.25
200	105	530	912.50
200	105	535	918.75
200	105	540	925.00
200	105	545	931.25
200	105	550	937.50
200	110	500	875.00
200	110	505	881.25
200	110	510	887.50
200	110	515	893.75
200	110	520	900.00
200	110	525	906.25
200	110	530	912.50
200	110	535	918.75
200	110	540	925.00
200	110	545	931.25
200	110	550	937.50
300	100	500	1000.00
300	100	505	1006.25
300	100	510	1012.50
300	100	515	1018.75
300	100	520	1025.00
300	100	525	1031.25
300	100	530	1037.50
300	100	535	1043.75
300	100	540	1050.00
300	100	545	1056.25
300	100	550	1062.50
300	105	500	1000.00
300	105	505	1006.25
300	105	510	1012.50
300	105	515	1018.75
300	105	520	1025.00
300	105	525	1031.25
300	105	530	1037.50
300	105	535	1043.75
300	105	540	1050.00
300	105	545	1056.25
300	105	550	1062.50
300	110	500	1000.00
300	110	505	1006.25
300	110	510	1012.50
300	110	515	1018.75
300	110	520	1025.00
300	110	525	1031.25
300	110	530	1037.50
300	110	535	1043.75
300	110	540	1050.00
300	110	545	1056.25
300	110	550	1062.50

 

[HighAndWilder]

How do I loop through data to create a chart with multiple Series. Then, how to I repeat that to create multiple charts, using that same methodology for the rest of the data?

Below is a table of fictional data, but the order of indexing is real. That is, at a given speed, suction pressure is held constant, and discharge pressure is increased. A program calculates the required HP. The suction pressure is increased, speed is still constant, and the discharge pressure is incremented, and HP is calculated.

Then the speed is increased to the next interval, and the process is repeated. I'm trying to figure out a way to loop through the day and create a chart for each speed range. In reality, there are too many data points to create all of the series on one chart. So I want to create the charts based on speed. I'm struggling with how to lay out the format for this. Should i used nested FOR loops? Should I use a Case structure? What's the best way to go about it? I haven't learned enough about Chart objects yet to understand how to populate a series using VBA.

I'm not looking for someone to do this for me. I really want to learn, and I'm only asking for guidance. Thank you in advance.

speed	Psuction	Pdischarge	Power
100	100	500	750.00
100	100	505	756.25
100	100	510	762.50
100	100	515	768.75
100	100	520	775.00
100	100	525	781.25
100	100	530	787.50
100	100	535	793.75
100	100	540	800.00
100	100	545	806.25
100	100	550	812.50
100	105	500	750.00
100	105	505	756.25
100	105	510	762.50
100	105	515	768.75
100	105	520	775.00
100	105	525	781.25
100	105	530	787.50
100	105	535	793.75
100	105	540	800.00
100	105	545	806.25
100	105	550	812.50
100	110	500	750.00
100	110	505	756.25
100	110	510	762.50
100	110	515	768.75
100	110	520	775.00
100	110	525	781.25
100	110	530	787.50
100	110	535	793.75
100	110	540	800.00
100	110	545	806.25
100	110	550	812.50
200	100	500	875.00
200	100	505	881.25
200	100	510	887.50
200	100	515	893.75
200	100	520	900.00
200	100	525	906.25
200	100	530	912.50
200	100	535	918.75
200	100	540	925.00
200	100	545	931.25
200	100	550	937.50
200	105	500	875.00
200	105	505	881.25
200	105	510	887.50
200	105	515	893.75
200	105	520	900.00
200	105	525	906.25
200	105	530	912.50
200	105	535	918.75
200	105	540	925.00
200	105	545	931.25
200	105	550	937.50
200	110	500	875.00
200	110	505	881.25
200	110	510	887.50
200	110	515	893.75
200	110	520	900.00
200	110	525	906.25
200	110	530	912.50
200	110	535	918.75
200	110	540	925.00
200	110	545	931.25
200	110	550	937.50
300	100	500	1000.00
300	100	505	1006.25
300	100	510	1012.50
300	100	515	1018.75
300	100	520	1025.00
300	100	525	1031.25
300	100	530	1037.50
300	100	535	1043.75
300	100	540	1050.00
300	100	545	1056.25
300	100	550	1062.50
300	105	500	1000.00
300	105	505	1006.25
300	105	510	1012.50
300	105	515	1018.75
300	105	520	1025.00
300	105	525	1031.25
300	105	530	1037.50
300	105	535	1043.75
300	105	540	1050.00
300	105	545	1056.25
300	105	550	1062.50
300	110	500	1000.00
300	110	505	1006.25
300	110	510	1012.50
300	110	515	1018.75
300	110	520	1025.00
300	110	525	1031.25
300	110	530	1037.50
300	110	535	1043.75
300	110	540	1050.00
300	110	545	1056.25
300	110	550	1062.50

Have you got an idea of what you want the chart to look like for any particular speed?

Can you produce one for us to look at?

 

[gshock]

Have you got an idea of what you want the chart to look like for any particular speed?

Can you produce one for us to look at?

Indeed, I can...

See below. Thanks.

 

[HighAndWilder]

Indeed, I can...

See below. Thanks.

View attachment 118255

I have put something together that may help you.

The raw data is in a table named tblRawChartData

In another sheet I have put this grid together from which the chart will get its data.

A drop down list in cell A1 enables the speed to be selected.

The chart changes when the speed is selected.

This just uses one chart.

You could create seperate charts for each speed and hard code the speed into the grid formula.

How to loop through data to generate charts.xlsm
	A	B	C	D	E	F
1	300
2				100	105	110
3			500	1000	1030	1060
4			505	1006.25	1036.25	1066.25
5			510	1012.5	1042.5	1072.5
6			515	1018.75	1048.75	1078.75
7			520	1025	1055	1085
8			525	1031.25	1061.25	1091.25
9			530	1037.5	1067.5	1097.5
10			535	1043.75	1073.75	1103.75
11			540	1050	1080	1110
12			545	1056.25	1086.25	1116.25
13			550	1062.5	1092.5	1122.5
DynamicChart

Cell Formulas
Range	Formula
C3:C13	C3	=UNIQUE(FILTER(tblRawChartData[Pdischarge],tblRawChartData[speed]=$A$1,""))
D3:F13	D3	=SUMIFS(tblRawChartData[Power],tblRawChartData[speed],$A$1,tblRawChartData[Psuction],D$2,tblRawChartData[Pdischarge],$C3)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A1	List	100,200,300

 

[gshock]

I have put something together that may help you.

The raw data is in a table named tblRawChartData

In another sheet I have put this grid together from which the chart will get its data.

A drop down list in cell A1 enables the speed to be selected.

The chart changes when the speed is selected.

This just uses one chart.

You could create seperate charts for each speed and hard code the speed into the grid formula.

How to loop through data to generate charts.xlsm
	A	B	C	D	E	F
1	300
2				100	105	110
3			500	1000	1030	1060
4			505	1006.25	1036.25	1066.25
5			510	1012.5	1042.5	1072.5
6			515	1018.75	1048.75	1078.75
7			520	1025	1055	1085
8			525	1031.25	1061.25	1091.25
9			530	1037.5	1067.5	1097.5
10			535	1043.75	1073.75	1103.75
11			540	1050	1080	1110
12			545	1056.25	1086.25	1116.25
13			550	1062.5	1092.5	1122.5
DynamicChart

Cell Formulas
Range	Formula
C3:C13	C3	=UNIQUE(FILTER(tblRawChartData[Pdischarge],tblRawChartData[speed]=$A$1,""))
D3:F13	D3	=SUMIFS(tblRawChartData[Power],tblRawChartData[speed],$A$1,tblRawChartData[Psuction],D$2,tblRawChartData[Pdischarge],$C3)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A1	List	100,200,300

View attachment 118276

That's not exactly what I was looking for, but that has some really interesting functionality that I could use for other things. Great job. Thank you.

If my goal is to populate series data using the X-axis and Y-axis values, how would I create the Legend Entries for the series, and the Series ranges? If the chart container in excel is for holding the chart object, are there other properties or methods to the chart that hold the chart elements?

 

[HighAndWilder]

Have you created a table from your data and set up the grid representing the X and Y axis for each series?

If you have then have you created a chart from this data?

The grid calculates the series data for the chart.

The Legend comes from the column headings on this grid.

How did you get the Legend on the chart that you posted in Post 3?

When you have created the chart you can click on any of the elements (Title, X and Y Axis, Legend and each Series) and change the properties.
Apart from Chart Title, all of the elements on the chart in Post 3.

It would be useful if you can post some realistic data using XL2BB.

 

[gshock]

Have you created a table from your data and set up the grid representing the X and Y axis for each series?

If you have then have you created a chart from this data?

The grid calculates the series data for the chart.

The Legend comes from the column headings on this grid.

How did you get the Legend on the chart that you posted in Post 3?

When you have created the chart you can click on any of the elements (Title, X and Y Axis, Legend and each Series) and change the properties.
Apart from Chart Title, all of the elements on the chart in Post 3.

It would be useful if you can post some realistic data using XL2BB.

I created the charts manually, which I have done many times. I set up the chart titles, axis titles, and series data as I needed them using the green "+" to the right of the chart. But I have so much data that I can't put it all on one chart. Because I have to create more than a few charts, it's not practical to have one chart that updates based on the dropdown selection (although, that's a really cool idea!).

I'm still learning VBA, but I know how to create new sheets in a workbook. I created the charts manually, and changed them from an object in a sheet to a new sheet with the speed name. Since that was a repetitious process, I figured I could automate it with VBA to loop through the data and define the X-axis and Y-axis values. I assumed that all I would need to do is identify the chart object somehow and then work with the properties or method of that object. Then, I planned to loop through the charts (collection?)

 

[HighAndWilder]

I created the charts manually, which I have done many times. I set up the chart titles, axis titles, and series data as I needed them using the green "+" to the right of the chart. But I have so much data that I can't put it all on one chart. Because I have to create more than a few charts, it's not practical to have one chart that updates based on the dropdown selection (although, that's a really cool idea!).

I'm still learning VBA, but I know how to create new sheets in a workbook. I created the charts manually, and changed them from an object in a sheet to a new sheet with the speed name. Since that was a repetitious process, I figured I could automate it with VBA to loop through the data and define the X-axis and Y-axis values. I assumed that all I would need to do is identify the chart object somehow and then work with the properties or method of that object. Then, I planned to loop through the charts (collection?)

You only need to create one chart which is used irrespective of what speed you need it to represent.

It is the values in the data ranges that the chart references that change. That is what the drop down is used for.

You don't need the drop down but you need to indicate somehow which speed you want the chart to use.

Examine the grid and see what the formula are achieving.