Forum Discussion

YeatsN's avatar
YeatsN
Copper Contributor
Jan 29, 2020

Formula that will adjust when I filter the spreadsheet

I have this formula to calculate the percentage of times "yes" is in a cell but don't know hot to change the formula so that it will re-calculate the percentage when I filter the spreadsheet.  This g...
Formulas and Functions
mathetes's avatar
mathetes
Silver Contributor
Jan 29, 2020

]YeatsN 

 

What you want to do is clear, but there's not enough info on the nature of the database where you want to do it. Is it possible to upload a sample that is representative of your actual database but without any real names?

  • YeatsN's avatar
    YeatsN
    Copper Contributor
    Jan 29, 2020

    mathetes 

     

    Attached is a spreadsheet to test.  In creating the test, some of the column letters changed but it's the same data.  Thanks for the help.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jan 29, 2020

      YeatsN 

      The pattern is here https://exceljet.net/formula/count-visible-rows-only-with-criteria

      Result is better to keep out of main table, when the formula could be

      =SUMPRODUCT(
   (Table10[$600 Production Rate or Better Achieved]="Yes") *
   SUBTOTAL(103,
      OFFSET(
          Table10[[#Headers],[$600 Production Rate or Better Achieved]],
          ROW(Table10[$600 Production Rate or Better Achieved]) -
          ROW(Table10[[#Headers],[$600 Production Rate or Better Achieved]]),
     0)
   )
) / AGGREGATE(3,1,Table10[$600 Production Rate or Better Achieved])

Resources