Formula to normalize a measurement then take an average of the normalized, all referencing an input search term.

[SURFER349]

So I need to figure out a formula to output the normalized average of each step.
For example, for each RUN, I need to normalize the measurement by the # of points: (measurement/#points)
then I want to take the average of all the Step 1 rows.

I tried using averageIF equation, but I can't use that to do the normalization first.
any thoughts?

Like this:

Step	Run	# of points	Measure1
1​	1​	24​	0.88​
1​	2​	25​	0.11​
1​	3​	25​	0.89​
1​	4​	25​	0.71​
1​	5​	23​	0.77​
2​	1​	22​	0.15​
2​	2​	20​	0.99​
2​	3​	19​	0.54​
2​	4​	25​	0.70​
2​	5​	17​	0.40​
3​	1​	20​	0.62​
3​	2​	21​	0.97​
3​	3​	25​	0.92​
3​	4​	2​	0.47​
3​	5​	25​	0.56​
4​	1​	25​	0.52​
4​	2​	25​	0.94​
4​	3​	24​	0.63​
4​	4​	23​	0.28​
4​	5​	21​	0.44​
5​	1​	24​	0.40​
5​	2​	25​	0.62​

 

[sandy666]

Power Query aka Get&Transform

• What is PowerQuery (Get&Transform)
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference (erratum required)

would be fine if you update your profile about Excel version

 

[SURFER349]

any way to do this just in a formula?

 

[sandy666]

any way to do this just in a formula?

maybe, I don't know

 

[sandy666]

maybe

Book1
	A	B	C	D	E	F
1	Step	Run	# of points	Measure1	Divide	Average
2	1	1	24	0.88	0.036667	0.027709
3	1	2	25	0.11	0.0044	0.027709
4	1	3	25	0.89	0.0356	0.027709
5	1	4	25	0.71	0.0284	0.027709
6	1	5	23	0.77	0.033478	0.027709
7	2	1	22	0.15	0.006818	0.027254
8	2	2	20	0.99	0.0495	0.027254
9	2	3	19	0.54	0.028421	0.027254
10	2	4	25	0.7	0.028	0.027254
11	2	5	17	0.4	0.023529	0.027254
12	3	1	20	0.62	0.031	0.074278
13	3	2	21	0.97	0.04619	0.074278
14	3	3	25	0.92	0.0368	0.074278
15	3	4	2	0.47	0.235	0.074278
16	3	5	25	0.56	0.0224	0.074278
17	4	1	25	0.52	0.0208	0.023555
18	4	2	25	0.94	0.0376	0.023555
19	4	3	24	0.63	0.02625	0.023555
20	4	4	23	0.28	0.012174	0.023555
21	4	5	21	0.44	0.020952	0.023555
22	5	1	24	0.4	0.016667	0.020733
23	5	2	25	0.62	0.0248	0.020733
Sheet1

Cell Formulas
Range	Formula
E2:E23	E2	=[@Measure1]/[@['# of points]]
F2:F23	F2	=AVERAGEIF([Step],[@Step],[Divide])

 

[SURFER349]

yea, this is what I'm running in to. Can that be combined into a single formula? It seems that this can only be done by creating a new "DIVIDE" column, then taking the average of that.

 

[sandy666]

maybe any formula master will help with single formula.
I quit the formula game a long time ago ?

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D			G
1	Step	Run	# of points	Measure1			Column1
2	1	1	24	0.88			0.02770899
3	1	2	25	0.11			0.02770899
4	1	3	25	0.89			0.02770899
5	1	4	25	0.71			0.02770899
6	1	5	23	0.77			0.02770899
7	2	1	22	0.15			0.02725373
8	2	2	20	0.99			0.02725373
9	2	3	19	0.54			0.02725373
10	2	4	25	0.7			0.02725373
11	2	5	17	0.4			0.02725373
12	3	1	20	0.62			0.0742781
13	3	2	21	0.97			0.0742781
14	3	3	25	0.92			0.0742781
15	3	4	2	0.47			0.0742781
16	3	5	25	0.56			0.0742781
17	4	1	25	0.52			0.02355526
18	4	2	25	0.94			0.02355526
19	4	3	24	0.63			0.02355526
20	4	4	23	0.28			0.02355526
21	4	5	21	0.44			0.02355526
22	5	1	24	0.4			0.02073333
23	5	2	25	0.62			0.02073333
Main

Cell Formulas
Range	Formula
G2:G23	G2	=SUMPRODUCT(([Step]=[@Step])*([Measure1]/['# of points]))/COUNTIFS([Step],[@Step])

 

[SURFER349]

I think this is getting really close! This is going to sound dumb, but I don't think I know enough about how to use table arrays to really make sense of this. The final issue I am running into is the output table will not have (per example) 23 rows, but a summary of steps 1 thru 5.

How to make a single row output for Step#1 that outputs the average of normalized data?

 

[Fluff]

Ok, how about

Book2.xlsx
	A	B	C	D	E	F										P	Q	R	S	T
1	Source Data																Average of runs=	avg of runs for each param
2
3	Step#	Run	QTY	Param1	Param2	Param3											Step#	Param1	Param2	Param3
4	1	1	22	1.10	0.32	27.97											1	0.0521	0.1591	1
5	1	2	24	0.16	0.95	14.07											2	0.1011	0.1456	1
6	1	3	15	1.01	0.27	7.91											3	0.1514	0.1461	1
7	1	4	15	2.31	0.72	49.06											4	0.1898	0.1164	1
8	1	5	25	0.31	0.91	45.07											5	0.2471	0.0694	1
9	2	1	16	0.99	0.55	4.96
10	2	2	19	1.33	0.46	36.55
11	2	3	25	2.51	0.79	43.44
12	2	4	21	4.05	0.75	45.35
13	2	5	20	4.81	0.21	19.99
14	3	1	19	1.57	0.49	8.00
15	3	2	20	1.49	0.41	46.26
16	3	3	16	4.37	0.46	19.14
17	3	4	22	2.37	0.07	9.67
18	3	5	24	0.29	0.38	28.08
19	4	1	20	4.80	0.37	38.06
20	4	2	22	2.21	0.74	22.42
21	4	3	19	2.13	0.69	44.52
22	4	4	24	3.18	0.91	27.19
23	5	1	17	3.31	0.97	32.12
24	5	2	25	4.07	0.04	40.86
Sheet1

Cell Formulas
Range	Formula
R4:T8	R4	=SUMPRODUCT(($A$4:$A$24=$Q4)*(A$4:A$24/$C$4:$C$24))/COUNTIFS($A$4:$A$24,$Q4)

 

[sandy666]

try
=IF([@Step]=A3,"",SUMPRODUCT(([Step]=[@Step])*([Measure1]/['# of points]))/COUNTIFS([Step],[@Step]))

Book1
	A	B	C	D	E
1	Step	Run	# of points	Measure1	Column1
2	1	1	24	0.88
3	1	2	25	0.11
4	1	3	25	0.89
5	1	4	25	0.71
6	1	5	23	0.77	0.027709
7	2	1	22	0.15
8	2	2	20	0.99
9	2	3	19	0.54
10	2	4	25	0.7
11	2	5	17	0.4	0.027254
12	3	1	20	0.62
13	3	2	21	0.97
14	3	3	25	0.92
15	3	4	2	0.47
16	3	5	25	0.56	0.074278
17	4	1	25	0.52
18	4	2	25	0.94
19	4	3	24	0.63
20	4	4	23	0.28
21	4	5	21	0.44	0.023555
22	5	1	24	0.4
23	5	2	25	0.62	0.020733
Sheet1

Cell Formulas
Range	Formula
E2:E23	E2	=IF([@Step]=A3,"",SUMPRODUCT(([Step]=[@Step])*([Measure1]/['# of points]))/COUNTIFS([Step],[@Step]))