CODEWITHSUNDEEP
stringexcelfunctionstatistics
BillyBoy
BillyBoy

Reputation: 111

Creating function via string in Excel

I am trying to find an easier way to calculate the following stats: average, std, min, and max. So far I have tried combining the function name (E9), start column ($E$6), Start row ($F$2), end column($G$6), and end row ($F$3). The result was AVERAGE(A2:C10). I want this to return the value of AVERAGE(A2:C10), which is 6.23. Is there anyway to turn this into an equation that Excel can recognize? This concept will be used to find the average, std, min, and max for a large dataset. Thanks in advance!

enter image description here

Upvotes: 1

Views: 65

Answers (2)

user4039065
user4039065

Reputation:

After some thinking about this, you could build a cross-reference table that provided the functionality of the formula with SUBTOTAL while using INDIRECT to provide the range.

Function   Function_Num   Function_Num (excludes hidden values)
Average    1              101
Min        5              105
StDev      7              107

A full list of available functionality with the SUBTOTAL function is available here. With that table in X1:Z4 your constructed formula would be,

=subtotal(vlookup(E9, X:Z, 2, false), indirect(e6&f3&":"&g6&f3))

Note that not every native worksheet function is available within SUBTOTAL but the three you have specified are as well as several others.

Addendum:

  1. A data Validation list could use the Source: list for E9:G9 as X2:X4 or a named range defined as ='Sheet1'!$X$2:INDEX('Sheet1'!$X:$X, MATCH("zzz", 'Sheet1'!$X:$X)) for a 'last alphabetic' lookup.
  2. The data lookup range itself could be defined as a dynamic named range with a Source: of =Sheet1!$X$2:INDEX(Sheet1!$Z:$Z, MATCH(1E+99, Sheet1!$Z:$Z)) for a 'last numeric' lookup.
  3. The <100 and >100 function_num identifiers both work the same on rows 'hidden' by the Data ► Sort & Filter ► Filter command.
  4. For Excel 2010 and higher, the AGGREGATE function may provide additional functionality.

Upvotes: 3

Byron Wall
Byron Wall

Reputation: 4010

If you want to wander into the world of undocumented "features" you can use the EVALUATE function if it is defined in a named range. It mostly works in Excel 2013 as advertised here: http://www.vertex42.com/ExcelArticles/evaluate-function.html.

The downside is that it appears it does not update unless the underlying formula or the result cell is updated. It does not properly respond to changes in the data in the AVERAGE/SUM on their own. This might not be an issue if you're changing the formula often (or using indirect with it since I believe INDIRECT forces full recalcs).

EVALUATE in action

Upvotes: 2

Related Questions