LINEST calculations

[JannetteChristie]

Hi,
I have the following calculation setup using the LINEST calculation.

However, I have a number of pumps defined - how do I get the LINEST auto calculate depending on the pump highlighted below:

Hope, I have maid some sense.
Thanks

 

[myall_blues]

Can you provide more information?

• Where is the LINEST formula and what is it (ie what cells does it reference)
• What is the significance of this picture (or indeed both pictures)?
• What do you mean by "auto calculate depending on the pump highlighted below"?

Can you provide some test data, ideally using XL2BB or just in a table. If it has to be screenshots then please include the row and column headers so we can see what refers to what in any formulas.

 

[JannetteChristie]

Hello,

I have now included the formulas being used.

Precision Rubber Restriction Curve Generator Rev3 (AH WIP).xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN
2			Un-Restricted Flow Data L/min														Un-restricted Head Data M														Curve Formualae
3			1	2	3	4	5	6	7	8	9	10	11	12	13	14	1	2	3	4	5	6	7	8	9	10	11	12	13	14
4	1	RV1502	0	32.051	64.103	96.154	128.205	160.256	192.308	224.359	256.41	288.462	320.513	352.564	384.615	384.615	27.137	26.634	26.057	25.26	24.423	23.719	22.558	21.428	19.836	18.01	15.647	12.829	9.698	9.698	-0.039304	0.001674	-4.29908E-05	5.33617E-07	-3.73364E-09	1.54583E-11	-3.76154E-14	4.97115E-17	-2.75235E-20	27.13698
5	2	RV1503	0	32.051	64.103	96.154	128.205	160.256	192.308	224.359	256.41	288.462	320.513	352.564	384.615	416.667	41.152	40.614	39.713	38.687	37.638	36.315	34.845	33.198	31.004	28.164	24.737	20.414	15.797	10.913
6	3	RV1504	0	32.051	64.103	96.154	128.205	160.256	192.308	224.359	256.41	288.462	320.513	352.564	384.615	416.667	55.078	54.395	53.228	51.889	50.526	48.787	46.864	44.68	41.813	38.028	33.507	27.745	21.58	15.046
7	4	RV1505	0	32.051	64.103	96.154	128.205	160.256	192.308	224.359	256.41	288.462	320.513	352.564	384.615	416.667	72.28	71.145	69.059	67.436	65.087	63.065	60.737	57.874	54.78	50.585	45.072	38.346	31.27	23.431
8	5	RV1506	0	32.051	64.103	96.154	128.205	160.256	192.308	224.359	256.41	288.462	320.513	352.564	384.615	416.667	88.045	86.69	84.74	83.102	80.485	78.16	75.604	72.503	69.32	64.515	57.93	50.635	42.027	32.574
9	6	RV1507	0	32.051	64.103	96.154	128.205	160.256	192.308	224.359	256.41	288.462	320.513	352.564	384.615	416.667	102.46	100.827	98.513	96.544	93.441	90.684	87.65	83.997	80.234	74.565	66.839	58.321	48.246	37.261
10	7	RV1508	0	32.051	64.103	96.154	128.205	160.256	192.308	224.359	256.41	288.462	320.513	352.564	384.615	416.667	116.897	114.989	112.311	110.015	106.429	103.247	99.736	95.529	91.185	84.653	75.783	66.038	54.509	41.981
11	8	RV1509	0	32.051	64.103	96.154	128.205	160.256	192.308	224.359	256.41	288.462	320.513	352.564	384.615	416.667	131.17	128.957	125.895	123.239	119.141	115.508	111.5	106.728	101.778	94.36	84.335	73.355	60.356	46.319
12	9	RV1510	0	32.051	64.103	96.154	128.205	160.256	192.308	224.359	256.41	288.462	320.513	352.564	384.615	416.667	147.258	145.005	141.757	139.059	134.726	130.884	126.67	121.546	116.303	108.371	97.464	85.312	70.992	55.192
13	10	RV1511	0	32.051	64.103	96.154	128.205	160.256	192.308	224.359	256.41	288.462	320.513	352.564	384.615	416.667	161.744	159.225	155.628	152.619	147.819	143.565	138.895	133.23	127.424	118.649	106.609	93.241	77.469	60.113
14	11	RV1512	0	32.051	64.103	96.154	128.205	160.256	192.308	224.359	256.41	288.462	320.513	352.564	384.615	416.667	176.185	173.398	169.444	166.114	160.844	156.173	151.039	144.828	138.453	128.832	115.646	101.062	83.833	64.935
15	12	RV1513	0	32.051	64.103	96.154	128.205	160.256	192.308	224.359	256.41	288.462	320.513	352.564	384.615	416.667	190.583	187.52	183.204	179.549	173.8	168.704	163.1	156.344	149.389	138.907	124.583	108.762	90.075	69.647
16	13	RV2002	0	37.179	74.359	111.538	148.718	185.897	223.077	260.256	297.436	334.615	371.795	408.974	446.154	474.177	29.722	29.38	28.683	27.901	27.245	26.563	25.802	24.652	23.415	21.556	19.173	16.282	12.649	9.749
17	14	RV2003	0	37.179	74.359	111.538	148.718	185.897	223.077	260.256	297.436	334.615	371.795	408.974	446.154	483.333	45.003	44.477	43.337	42.42	41.378	40.341	38.919	37.501	35.706	32.77	29.18	24.653	19.721	13.821
18	15	RV2004	0	37.179	74.359	111.538	148.718	185.897	223.077	260.256	297.436	334.615	371.795	408.974	446.154	483.333	62.244	60.995	59.855	58.561	56.586	54.816	53.414	51.19	49.193	45.771	41.015	35.32	28.871	22.044
19	16	RV2005	0	37.179	74.359	111.538	148.718	185.897	223.077	260.256	297.436	334.615	371.795	408.974	446.154	483.333	78.57	77.092	75.776	74.278	71.915	69.786	68.14	65.434	63.053	58.953	53.253	46.18	38.157	29.646
20	17	RV2006	0	37.179	74.359	111.538	148.718	185.897	223.077	260.256	297.436	334.615	371.795	408.974	446.154	483.333	94.214	92.932	91.528	89.93	87.439	84.894	82.843	80.146	77.532	72.709	65.632	57.322	47.81	37.287
21	18	RV2007	0	37.179	74.359	111.538	148.718	185.897	223.077	260.256	297.436	334.615	371.795	408.974	446.154	483.333	109.537	107.997	106.296	104.353	101.374	98.348	95.896	92.699	89.562	83.826	75.491	65.774	54.623	42.36
22	19	RV2008	0	37.179	74.359	111.538	148.718	185.897	223.077	260.256	297.436	334.615	371.795	408.974	446.154	483.333	126.472	124.817	123.026	121.01	117.8	114.506	111.883	108.404	105.094	98.899	89.655	78.632	66.067	51.981
23	20	RV2009	0	37.179	74.359	111.538	148.718	185.897	223.077	260.256	297.436	334.615	371.795	408.974	446.154	483.333	141.999	140.11	138.059	135.746	132.095	128.355	125.369	121.417	117.639	110.591	100.128	87.708	73.53	57.706
24	21	RV2010	0	37.179	74.359	111.538	148.718	185.897	223.077	260.256	297.436	334.615	371.795	408.974	446.154	483.333	157.463	155.335	153.016	150.394	146.293	142.101	138.743	134.312	130.048	122.132	110.433	96.609	80.815	63.25
25	22	RV2011	0	37.179	74.359	111.538	148.718	185.897	223.077	260.256	297.436	334.615	371.795	408.974	446.154	483.333	173.967	171.639	169.104	166.249	161.757	157.149	153.468	148.608	143.971	135.346	122.544	107.358	90.035	70.683
26	23	RV2012	0	37.179	74.359	111.538	148.718	185.897	223.077	260.256	297.436	334.615	371.795	408.974	446.154	483.333	189.456	186.886	184.077	180.909	175.961	170.895	166.835	161.49	156.362	146.859	132.822	116.226	97.289	76.199
27	24	RV3001	0	58.974	117.949	176.923	235.897	294.872	353.846	412.821	471.795	530.769	589.744	648.718	707.692	766.667	24.786	23.663	22.834	21.929	21.124	20.551	19.75	18.672	17.744	16.626	15.154	13.398	11.315	8.876
28	25	RV3002	0	58.974	117.949	176.923	235.897	294.872	353.846	412.821	471.795	530.769	589.744	648.718	707.692	766.667	48.46	46.629	45.382	44.468	43.702	42.529	41.179	39.667	37.893	35.83	33.284	30.112	26.745	22.745
29	26	RV3003	0	58.974	117.949	176.923	235.897	294.872	353.846	412.821	471.795	530.769	589.744	648.718	707.692	766.667	72.192	69.356	67.446	65.995	64.732	62.862	60.754	58.408	55.683	52.538	48.645	43.856	38.801	32.736
30	27	RV3004	0	58.974	117.949	176.923	235.897	294.872	353.846	412.821	471.795	530.769	589.744	648.718	707.692	766.667	99.368	96.471	93.863	91.393	88.97	86.562	84.052	81.189	77.966	74.39	69.894	64.149	57.602	50.308
31	28	RV3005	0	58.974	117.949	176.923	235.897	294.872	353.846	412.821	471.795	530.769	589.744	648.718	707.692	766.667	121.07	117.209	113.522	111.906	109.3	106.521	103.337	99.549	95.222	90.635	84.909	77.484	68.864	60.033
32	29	RV3006	0	58.974	117.949	176.923	235.897	294.872	353.846	412.821	471.795	530.769	589.744	648.718	707.692	766.667	145.646	140.996	136.549	134.588	131.433	128.071	124.226	119.66	114.445	108.925	102.052	93.144	82.797	72.207
33	30	RV3007	0	58.974	117.949	176.923	235.897	294.872	353.846	412.821	471.795	530.769	589.744	648.718	707.692	766.667	169.214	163.738	158.51	156.136	152.333	148.305	143.723	138.297	132.151	125.653	117.534	107.067	95.018	82.577
34	31	RV4501	0	84.615	169.231	253.846	338.462	423.077	507.692	592.308	676.923	761.538	846.154	930.769	1015.385	1100	19.647	19.147	18.808	18.567	18.146	17.567	16.72	15.734	14.399	12.903	10.985	8.966	6.535	3.837
35	32	RV4502	0	84.615	169.231	253.846	338.462	423.077	507.692	592.308	676.923	761.538	846.154	930.769	1015.385	1100	48.987	48.284	47.991	47.173	45.848	44.426	43.033	41.519	39.47	36.847	33.861	30.531	26.741	22.238
36	33	RV4503	0	84.615	169.231	253.846	338.462	423.077	507.692	592.308	676.923	761.538	846.154	930.769	1015.385	1100	74.206	73.078	72.899	71.876	70.035	68.091	66.293	64.401	61.747	58.257	54.157	49.599	44.53	38.524
37	34	RV4504	0	84.615	169.231	253.846	338.462	423.077	507.692	592.308	676.923	761.538	846.154	930.769	1015.385	1100	97.459	96.431	96.455	95.323	93.072	90.553	88.157	85.607	82.389	77.779	72.392	66.137	59.52	51.415
38	35	RV4505	0	84.615	169.231	253.846	338.462	423.077	507.692	592.308	676.923	761.538	846.154	930.769	1015.385	1100	122.255	121.02	121.162	119.852	117.131	114.08	111.188	108.099	104.207	98.582	91.913	84.151	75.945	65.897
39	36	RV4506	0	84.615	169.231	253.846	338.462	423.077	507.692	592.308	676.923	761.538	846.154	930.769	1015.385	1100	146.02	144.506	144.594	142.942	139.612	135.887	132.351	128.576	123.808	116.945	108.9	99.544	89.646	77.515
40	37	RV4507	0	84.615	169.231	253.846	338.462	423.077	507.692	592.308	676.923	761.538	846.154	930.769	1015.385	1100	173.013	171.392	171.846	170.262	166.654	162.573	158.717	154.587	149.407	141.857	132.635	121.908	110.518	96.706
41	38	RV4508	0	84.615	169.231	253.846	338.462	423.077	507.692	592.308	676.923	761.538	846.154	930.769	1015.385	1100	197.524	195.656	196.135	194.283	190.124	185.421	180.971	176.213	170.238	161.554	150.991	138.702	125.674	109.841
42
43
Pump Data

Cell Formulas
Range	Formula
P4,AD4	P4	=O4
AE4:AN4	AE4	=LINEST(Sheet2!$C$16:$C$29,Sheet2!$C$2:$C$15^{9,8,7,6,5,4,3,2,1})
Press CTRL+SHIFT+ENTER to enter array formulas.

Precision Rubber Restriction Curve Generator Rev3 (AH WIP).xlsm
	B	C	D	E	F
1		RV1502	RV1503	RV1504	RV1505
2	1	0	0	0	0
3	2	32.051	32.051	32.051	32.051
4	3	64.103	64.103	64.103	64.103
5	4	96.154	96.154	96.154	96.154
6	5	128.205	128.205	128.205	128.205
7	6	160.256	160.256	160.256	160.256
8	7	192.308	192.308	192.308	192.308
9	8	224.359	224.359	224.359	224.359
10	9	256.41	256.41	256.41	256.41
11	10	288.462	288.462	288.462	288.462
12	11	320.513	320.513	320.513	320.513
13	12	352.564	352.564	352.564	352.564
14	13	384.615	384.615	384.615	384.615
15	14	384.615	416.667	416.667	416.667
16	1	27.137	41.152	55.078	72.28
17	2	26.634	40.614	54.395	71.145
18	3	26.057	39.713	53.228	69.059
19	4	25.26	38.687	51.889	67.436
20	5	24.423	37.638	50.526	65.087
21	6	23.719	36.315	48.787	63.065
22	7	22.558	34.845	46.864	60.737
23	8	21.428	33.198	44.68	57.874
24	9	19.836	31.004	41.813	54.78
25	10	18.01	28.164	38.028	50.585
26	11	15.647	24.737	33.507	45.072
27	12	12.829	20.414	27.745	38.346
28	13	9.698	15.797	21.58	31.27
29	14	9.698	10.913	15.046	23.431
Sheet2

Cell Formulas
Range	Formula
C15,C29	C15	=C14

I am trying to update the sheet 'Pump Data' using the pump in column B with the corresponding values from Sheet 2 with the pump info in row 1 columns C,D,E etc

Thanks

 

[myall_blues]

Can you update your profile to show what version of Excel you are using?

 

[JannetteChristie]

Hello,
I am not sure how to update my profile to show the version - I am using Office 365.

Thanks

 

[myall_blues]

OK thanks.

Try this:

=LINEST(INDEX(Sheet2!$C$16:$F$29,0,MATCH($B4,Sheet2!$C$1:$F$1,0)),INDEX(Sheet2!$C$2:$F$15,0,MATCH($B4,Sheet2!$C$1:$F$1,0))^{9,8,7,6,5,4,3,2,1})

If you're using 365 you don't need to enter it as an array formula.

To update your profile click on your avatar/name in the top right corner, then click account details, and towards the bottom of that window you can tick the box for the version you are using. It just makes it easier for providing solutions knowing what functions can be used.

Hope this helps.