How to calculate % change and then the average from individual cells

[raul8]

Hello again community!! I have yet another eye turning calculation to make which i need help with.
In the past i got help to construct this formula: SUM(SUMIFS(H2:H18,B2:B18,{"*Hold*","*P5*","*P6*"},M2:M18,"*BP*",H2:H18,">0"))/SUM(SUMIFS(G2:G18,B2:B18,{"*Hold*","*P5*","*P6*"},M2:M18,"*BP*",G2:G18,">0"))-1 which using the sample data below it returns a value of 0.038851 but the expected return should be 0.546164. The formula works right given its parameters but i misunderstood the parameters. So here is what the formula should calculate:

In a sheet with 90 rows, the formula needs to first filter the data set to select the rows that meet the criteria from column B (if they contain 'Hold', 'P5', 'P6') and the criteria from column M (if they contain 'BP'), then calculate the % change btwn 2 numbers [(Column H / Colum G)-1] on each row and then calculate the average of the %s that are >0. To better represent the ask, on the data set below, i added column I as a helper column. So on this sample the final # to return should be the average of the rows highlighted in yellow because their calculated % are >0, all others need to be ignored.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ID	Board Column	Title	Assigned To	Priority	Delivery D	Original Expected Cost	Expected Total Cost	Helper Column	Health	Work Item T	State	Area Path
2	77864	Hold	x		2 - High	x	150000	228000	0.52	Green	E	New	Management\BP
3	99698	P3-	x		3 - Medium	x	113000	113000	0	Green	E	Active	Management\BP
4	71321	P4-	x		3 - Medium	x	58000	58000	0	Green	E	Active	Management\BP
5	106537	P5-	x		2 - High	x	55000	49000	-0.10909	Green	E	Active	Management\BP
6	99730	P5-	x		1 - Critical	x	800000	800000	0	Green	E	Active	Management\BP
7	78140	P5-	x		1 - Critical	x	159000	250000	0.572327	Green	E	Active	Management\BP
8	114022	P5-	x		4 - Low	x	143480	143480	0	Green	E	Active	Management\BP
9	100849	P5-	x		3 - Medium	x	66000	66000	0	Green	E	Active	Management\BP
10	68387	P5-	x		2 - High	x	125000	125000	0	Yellow	E	Active	Management\BP
11	106256	P5-	x		3 - Medium	x	120000	120000	0	Green	E	Active	Management\BP
12	108583	P5-	x		3 - Medium	x	163000	163000	0	Green	E	Active	Management\PK
13	100802	P5-	x		2 - High	x	218000	218000	0	Green	E	Active	Management\PK
14	106791	P5-	x		2 - High	x	950000	950000	0	Yellow	E	Active	Management\BP
15	99277	P5-	x		2 - High	x	1300000	1300000	0	Green	E	Active	Management\BP
16	94233	P5-	x		3 - Medium	x	125000	125000	0	Green	E	Active	Management\BP
17	55611	P6-	x		3 - Medium	x	72000	72000	0	Green	E	Active	Management\BP
18	78728	P6-	x		2 - High	x	130000	130000	0	Yellow	E	Active	Management\BP
19
20									0.546164
21									#VALUE!
Sheet1

Cell Formulas
Range	Formula
I2:I18	I2	=(H2/G2)-1
I20	I20	=AVERAGE(I2,I7)
I21	I21	=AVERAGE(I2:I18,">0")

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[alansidman]

To help you better understand my solution and Power Query

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.