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

raul8

New Member
Joined
Sep 21, 2021
Messages
36
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Web
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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
I'd stick with the helper column & then use
Excel Formula:
=AVERAGEIF(I2:I18,">0")
 
Upvote 0
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"
 
Upvote 0
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.
 
Upvote 0
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)))
 
Upvote 0
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
1635868449885.png
 
Upvote 0
That's not VBA it's for Power Query, aka Get & transform on the data tab.
 
Upvote 0
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?
 
Upvote 0
How about
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)))
 
Upvote 0
Solution
How about
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!!! :)
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top