New maths & stats functions
Like MAXIF, MINIF and SUMPRODUCT that works on Row x Column (currently need to rely on MMULT even for 1 dimensional arrays or use TRANSPOSE)
Dhruva Poonia
shared this idea
5 comments
-
Darren Van Slyke
commented
The only way to do conditional subtotals of data in a table or so filters are recognized is to do a crazy formula like this:
=SUMPRODUCT(SUBTOTAL(3,OFFSET($S75:$S77,ROW($S75:$S77)-MIN(ROW($S75:$S77)),,1)),($S75:$S77="M")*(Z75:Z77)*($AQ75:$AQ77="S10"))
Surely an easier formula variable could be developed that sums up data from a range when conditions are met AND the result is recognized by filters/tables. "SUMIF" should do it, but it doesn't work with filters.
-
Cameron_CSA
commented
Weighted average formula to avoid having to use a sumproduct formula then divide by the sum of one of the arraies. IE WAverage(AverageRange,WeightingRange)
-
Kenneth Barber commented
-
Brett Ables
commented
More advanced Interpolation, integration, regression, filtering.... take a look at Python's scipy or Matlab's capabilities...
-
Cathy Harley (EXCEL PM)
commented
Thanks for the feedback! We're doing a bit of work in this area right now! What other math and stats functions would make your lives easier (any examples would be awesome!)?