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

#### raul8

##### New Member
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
ABCDEFGHIJKLM
1IDBoard ColumnTitleAssigned ToPriorityDelivery DOriginal Expected CostExpected Total CostHelper ColumnHealthWork Item TStateArea Path
277864Holdx2 - Highx1500002280000.52GreenENewManagement\BP
399698P3-x3 - Mediumx1130001130000GreenEActiveManagement\BP
471321P4-x3 - Mediumx58000580000GreenEActiveManagement\BP
5106537P5-x2 - Highx5500049000-0.10909GreenEActiveManagement\BP
699730P5-x1 - Criticalx8000008000000GreenEActiveManagement\BP
778140P5-x1 - Criticalx1590002500000.572327GreenEActiveManagement\BP
8114022P5-x4 - Lowx1434801434800GreenEActiveManagement\BP
9100849P5-x3 - Mediumx66000660000GreenEActiveManagement\BP
1068387P5-x2 - Highx1250001250000YellowEActiveManagement\BP
11106256P5-x3 - Mediumx1200001200000GreenEActiveManagement\BP
12108583P5-x3 - Mediumx1630001630000GreenEActiveManagement\PK
13100802P5-x2 - Highx2180002180000GreenEActiveManagement\PK
14106791P5-x2 - Highx9500009500000YellowEActiveManagement\BP
1599277P5-x2 - Highx130000013000000GreenEActiveManagement\BP
1694233P5-x3 - Mediumx1250001250000GreenEActiveManagement\BP
1755611P6-x3 - Mediumx72000720000GreenEActiveManagement\BP
1878728P6-x2 - Highx1300001300000YellowEActiveManagement\BP
19
200.546164
21#VALUE!
Sheet1
Cell Formulas
RangeFormula
I2:I18I2=(H2/G2)-1
I20I20=AVERAGE(I2,I7)
I21I21=AVERAGE(I2:I18,">0")

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### Fluff

##### MrExcel MVP, Moderator
I'd stick with the helper column & then use
Excel Formula:
``=AVERAGEIF(I2:I18,">0")``

#### alansidman

##### Well-known Member
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.

Power Query:
``````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

##### New Member
I'd stick with the helper column & then use
Excel Formula:
``=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

##### MrExcel MVP, Moderator
Not sure what that's got to do with helper columns, but if you don't want them & have the LET function, try
Excel Formula:
``=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

##### New Member
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.

Power Query:
``````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

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

#### raul8

##### New Member
Not sure what that's got to do with helper columns, but if you don't want them & have the LET function, try
Excel Formula:
``=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

##### MrExcel MVP, Moderator
Excel Formula:
``=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

##### New Member
Excel Formula:
``=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!!! Replies
1
Views
156
Replies
4
Views
403
Replies
3
Views
2K
Replies
15
Views
473
Replies
8
Views
391

### Forum statistics

1,195,932
Messages
6,012,380
Members
441,693
Latest member ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back