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]

I'd stick with the helper column & then use

=AVERAGEIF(I2:I18,">0")

 

[alansidman]

If you don't have the helper column, then you could use Power Query, but to be honest, I like Fluff's answer better than mine. But use the following to generate the helper column.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Contains([Board Column],"P4") or Text.Contains([Board Column],"P5") or Text.Contains([Board Column],"Hold") and Text.Contains([Area Path],"BP") then 1 else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Removed Columns", "Subtraction", each [Expected Total Cost] - [Original Expected Cost], type number),
    #"Inserted Division" = Table.AddColumn(#"Inserted Subtraction", "Division", each [Subtraction] / [Original Expected Cost], type number),
    #"Changed Type" = Table.TransformColumnTypes(#"Inserted Division",{{"Division", Percentage.Type}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each [Division] > 0)
in
    #"Filtered Rows1"

 

[raul8]

I'd stick with the helper column & then use

=AVERAGEIF(I2:I18,">0")

Due to the nature of the data source, to automate and reduce/eliminate human manipulation/error, we can't use a helper column.

 

[Fluff]

Not sure what that's got to do with helper columns, but if you don't want them & have the LET function, try

=LET(f,FILTER((H2:H18/G2:G18)-1,((ISNUMBER(SEARCH("Hold",B2:B18)))+(ISNUMBER(SEARCH("P5",B2:B18)))+(ISNUMBER(SEARCH("P6",B2:B18))))*(ISNUMBER(SEARCH("BP",M2:M18)))),AVERAGE(FILTER(f,f>0)))

 

[raul8]

If you don't have the helper column, then you could use Power Query, but to be honest, I like Fluff's answer better than mine. But use the following to generate the helper column.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Contains([Board Column],"P4") or Text.Contains([Board Column],"P5") or Text.Contains([Board Column],"Hold") and Text.Contains([Area Path],"BP") then 1 else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Removed Columns", "Subtraction", each [Expected Total Cost] - [Original Expected Cost], type number),
    #"Inserted Division" = Table.AddColumn(#"Inserted Subtraction", "Division", each [Subtraction] / [Original Expected Cost], type number),
    #"Changed Type" = Table.TransformColumnTypes(#"Inserted Division",{{"Division", Percentage.Type}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each [Division] > 0)
in
    #"Filtered Rows1"

Thank you. This solution could work given that if i could code only that cell into the template file. I know I would have to then save that file as macro enabled, and that would be ok. If you help me further with the VBA coding

 

[Fluff]

That's not VBA it's for Power Query, aka Get & transform on the data tab.

 

[raul8]

Not sure what that's got to do with helper columns, but if you don't want them & have the LET function, try

=LET(f,FILTER((H2:H18/G2:G18)-1,((ISNUMBER(SEARCH("Hold",B2:B18)))+(ISNUMBER(SEARCH("P5",B2:B18)))+(ISNUMBER(SEARCH("P6",B2:B18))))*(ISNUMBER(SEARCH("BP",M2:M18)))),AVERAGE(FILTER(f,f>0)))

Thank you, this formula works!!! but i noticed that if both columns G and H are blank, i get a #DIV/0! return, how can we get around that?

 

[Fluff]

How about

=LET(f,FILTER(IFERROR((H2:H18/G2:G18)-1,0),((ISNUMBER(SEARCH("Hold",B2:B18)))+(ISNUMBER(SEARCH("P5",B2:B18)))+(ISNUMBER(SEARCH("P6",B2:B18))))*(ISNUMBER(SEARCH("BP",M2:M18)))),AVERAGE(FILTER(f,f>0)))

 

[raul8]

How about

=LET(f,FILTER(IFERROR((H2:H18/G2:G18)-1,0),((ISNUMBER(SEARCH("Hold",B2:B18)))+(ISNUMBER(SEARCH("P5",B2:B18)))+(ISNUMBER(SEARCH("P6",B2:B18))))*(ISNUMBER(SEARCH("BP",M2:M18)))),AVERAGE(FILTER(f,f>0)))

YES!!! thank you very much!!!