Carrying out LINEST to selections of data in 2 columns

[AL3410]

[TABLE="width: 286"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Power (kW)[/TD]
[TD]Head (m)[/TD]
[TD]Mass Flow (kg/s)[/TD]
[/TR]
[TR]
[TD="align: right"]51.58[/TD]
[TD="align: right"]30.00[/TD]
[TD="align: right"]206.1922[/TD]
[/TR]
[TR]
[TD="align: right"]92.2492[/TD]
[TD="align: right"]30.00[/TD]
[TD="align: right"]401.8627[/TD]
[/TR]
[TR]
[TD="align: right"]62.2747[/TD]
[TD="align: right"]30.00[/TD]
[TD="align: right"]243.2216[/TD]
[/TR]
[TR]
[TD="align: right"]83.2188[/TD]
[TD="align: right"]30.00[/TD]
[TD="align: right"]325.0214[/TD]
[/TR]
[TR]
[TD="align: right"]42.074[/TD]
[TD="align: right"]30.00[/TD]
[TD="align: right"]174.3451[/TD]
[/TR]
[TR]
[TD="align: right"]36.3087[/TD]
[TD="align: right"]30.00[/TD]
[TD="align: right"]154.2164[/TD]
[/TR]
[TR]
[TD="align: right"]48.1465[/TD]
[TD="align: right"]30.00[/TD]
[TD="align: right"]194.7579[/TD]
[/TR]
[TR]
[TD="align: right"]31.211[/TD]
[TD="align: right"]30.00[/TD]
[TD="align: right"]135.9637[/TD]
[/TR]
[TR]
[TD="align: right"]56.457[/TD]
[TD="align: right"]30.00[/TD]
[TD="align: right"]223.0638[/TD]
[/TR]
[TR]
[TD="align: right"]92.6242[/TD]
[TD="align: right"]30.00[/TD]
[TD="align: right"]414.1147[/TD]
[/TR]
[TR]
[TD="align: right"]87.469[/TD]
[TD="align: right"]30.00[/TD]
[TD="align: right"]349.6592[/TD]
[/TR]
[TR]
[TD="align: right"]86.2473[/TD]
[TD="align: right"]30.00[/TD]
[TD="align: right"]340.7664[/TD]
[/TR]
[TR]
[TD="align: right"]91.2766[/TD]
[TD="align: right"]30.00[/TD]
[TD="align: right"]387.6852[/TD]
[/TR]
[TR]
[TD="align: right"]88.804[/TD]
[TD="align: right"]30.00[/TD]
[TD="align: right"]359.2220[/TD]
[/TR]
[TR]
[TD="align: right"]90.3048[/TD]
[TD="align: right"]30.00[/TD]
[TD="align: right"]374.2025[/TD]
[/TR]
[TR]
[TD="align: right"]62.1384[/TD]
[TD="align: right"]30.10[/TD]
[TD="align: right"]241.8830[/TD]
[/TR]
[TR]
[TD="align: right"]83.9008[/TD]
[TD="align: right"]30.10[/TD]
[TD="align: right"]326.5964[/TD]
[/TR]
[TR]
[TD="align: right"]42.0985[/TD]
[TD="align: right"]30.10[/TD]
[TD="align: right"]173.8670[/TD]
[/TR]
[TR]
[TD="align: right"]48.1575[/TD]
[TD="align: right"]30.10[/TD]
[TD="align: right"]194.1553[/TD]
[/TR]
[TR]
[TD="align: right"]36.3319[/TD]
[TD="align: right"]30.10[/TD]
[TD="align: right"]153.8022[/TD]
[/TR]
[TR]
[TD="align: right"]31.2296[/TD]
[TD="align: right"]30.10[/TD]
[TD="align: right"]135.5927[/TD]
[/TR]
[TR]
[TD="align: right"]56.3838[/TD]
[TD="align: right"]30.10[/TD]
[TD="align: right"]222.0345[/TD]
[/TR]
[TR]
[TD="align: right"]51.5859[/TD]
[TD="align: right"]30.10[/TD]
[TD="align: right"]205.5306[/TD]
[/TR]
[TR]
[TD="align: right"]93.1926[/TD]
[TD="align: right"]30.10[/TD]
[TD="align: right"]415.2717[/TD]
[/TR]
[TR]
[TD="align: right"]92.8408[/TD]
[TD="align: right"]30.10[/TD]
[TD="align: right"]403.0963[/TD]
[/TR]
[TR]
[TD="align: right"]88.0979[/TD]
[TD="align: right"]30.10[/TD]
[TD="align: right"]351.0032[/TD]
[/TR]
[TR]
[TD="align: right"]86.8729[/TD]
[TD="align: right"]30.10[/TD]
[TD="align: right"]342.0979[/TD]
[/TR]
[TR]
[TD="align: right"]90.9097[/TD]
[TD="align: right"]30.10[/TD]
[TD="align: right"]375.4576[/TD]
[/TR]
[TR]
[TD="align: right"]91.8482[/TD]
[TD="align: right"]30.10[/TD]
[TD="align: right"]388.8169[/TD]
[/TR]
[TR]
[TD="align: right"]89.4133[/TD]
[TD="align: right"]30.10[/TD]
[TD="align: right"]360.4851[/TD]
[/TR]
[TR]
[TD="align: right"]62.0035[/TD]
[TD="align: right"]30.20[/TD]
[TD="align: right"]240.5587[/TD]
[/TR]
[TR]
[TD="align: right"]84.5831[/TD]
[TD="align: right"]30.20[/TD]
[TD="align: right"]328.1621[/TD]
[/TR]
[TR]
[TD="align: right"]42.1229[/TD]
[TD="align: right"]30.20[/TD]
[TD="align: right"]173.3918[/TD]
[/TR]
[TR]
[TD="align: right"]48.1698[/TD]
[TD="align: right"]30.20[/TD]
[TD="align: right"]193.5618[/TD]
[/TR]
[TR]
[TD="align: right"]36.3554[/TD]
[TD="align: right"]30.20[/TD]
[TD="align: right"]153.3921[/TD]
[/TR]
[TR]
[TD="align: right"]31.2508[/TD]
[TD="align: right"]30.20[/TD]
[TD="align: right"]135.2355[/TD]
[/TR]
[TR]
[TD="align: right"]56.3108[/TD]
[TD="align: right"]30.20[/TD]
[TD="align: right"]221.0127

[/TD]
[/TR]
</tbody>[/TABLE]

The data I have looks like above, with the Head increasing by 0.1m after a changing number of rows. What I would like to do is to select the Power and Mass flow rate where the head is constant, I.e all of the power to the left of 30.00 and all of the mass flow for 30.00 and produce coefficients of a 6th order polynomial which for 30.00 looks like :[TABLE="width: 596"]
<colgroup><col><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD]Head (m)[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD] A[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD="align: right"] 1.26958E-07[/TD]
[TD="align: right"]-4.41975E-05[/TD]
[TD="align: right"]0.0062494 [/TD]
[TD="align: right"]-0.45855[/TD]
[TD="align: right"]18.3944 [/TD]
[TD="align: right"]-378.8121[/TD]
[TD="align: right"]3243.88[/TD]
[/TR]
</tbody>[/TABLE]

As the data goes down, there are different numbers of rows with constant head value, i.e 14 rows of 30.00m and 7 for 35m. I am trying to run a macro that will give me 150 rows of the coefficients of the 6th order fit line for each head value.

I have probably explained this in a very roundabout way, but any help would be much appreciated!

 

[sijpie]

your table is clear and the polynomal is clear but exactly what you want as output is not very clear. I understand you want to lump the data together for each different Head figure. But how? Can you show a small example what it should look like?