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

raul8

New Member
Joined
Sep 21, 2021
Messages
35
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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,955
Office Version
  1. 365
Platform
  1. Windows
I'd stick with the helper column & then use
Excel Formula:
=AVERAGEIF(I2:I18,">0")
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,762
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 21, 2021
Messages
35
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Web
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
Joined
Jun 12, 2014
Messages
67,955
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,955
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

raul8

New Member
Joined
Sep 21, 2021
Messages
35
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Web
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
Joined
Jun 12, 2014
Messages
67,955
Office Version
  1. 365
Platform
  1. Windows
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)))
 
Solution

raul8

New Member
Joined
Sep 21, 2021
Messages
35
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Web
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!!! :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,853
Messages
5,766,776
Members
425,378
Latest member
kapoor2892

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
Top