Ev function

Miscellaneous category - Excel Information family Home 
What the name means"BF Indirect function equivalent"
SyntaxEv ( Textstring_s, [DefaultValue], [Options] )
DescriptionEvaluate. Like Excel's INDIRECT but it can accept a whole formula (eg MyRangeName*2), not just a reference (eg A23) or a range name (eg MyRangeName). You can also set a default value if the string is a blank or an error.
VolatilityThis function is termed 'volatile', which means it recalculates whenever the worksheet is recalculated, regardless of whether any of its inputs have changed. See Volatility.
Auto Multi FunctionThis function is an 'AutoMulti' function and can accept range inputs as well as single values for the variables indicated AM, in which case the function will perform its calculation on each of the values in the range. See Auto Multi Functions. In this case the function can repeat its calculations for each corresponding value of Textstring_s.
See also Excel functionsINDIRECT (Related)
VariableType or ValueDescription
Inputs: (3)
1 Textstring_s Range Of Strings  AM Either one string or a range of strings (text)
2 DefaultValue Any (Optional) What the function should default to (Optional)
3 Options Integer (Optional) A number comprising either the option number you require or an additive combination of option numbers. See function narrative for details. (Optional)
Output:
Return Value TheRange Any A range of any type and size
Defaults and Values

The variable(s) of this function have certain defaults and/or switch values associated with them. Click here to see more.

Values for 'Options' (or 'Opts')
  • 1. Use the DefaultValue if Textstring is an error (eg #NUM!, #NA etc). (Normal behaviour: throw an error)
  • 2. Use the DefaultValue if the reference in Textstring is an error or is invalid (ie Textstring was OK, but the thing its pointing at is an error or its an invalid reference). (Normal behaviour: throw an error)
The function will always choose the default if Textstring is missing or blank and a DefaultValue is available.
These are options are additive eg 1+2 etc.
If you want to use the default under any error BF can detect, set Options to 3
For Excel Experts
Note that, because this function is like INDIRECT(), it also is Volatile, which means that it is always recalculated when the worksheet recalculates, regardless of whether the input the data has changed. This is because Excel has no way of knowing whether the cell ranges that Textstring_s are pointing to have changed.
Key Points
Usually only one Textstring is entered. If you enter more than one (sometimes called Automulti functionaility in the BF documentation), then each Textstring is dealt with in turn, and the results are added together.
If Options is not specified or is zero, the function behaves similar to Excel's INDIRECT() function.
If the function needs a DefaultValue and doesn't find one, it will throw an error.
Tip
  • This function MUST receive either text or a number as an input eg "A12" not A12. This is a common misunderstanding.
  • A new feature (April 09) is that if this function receives a number, it will simply return that number. That means that you can either give this function a rangename or a number.
Since this functions is like INDIRECT in that it takes a text reference and looks it up, there is a fair chance you will get errors from time to time if you specify a reference that does not exist. If the EV() functions are buried in a long formula and there are lots of them, it can be tricky to find which one is causing the problem. To isolate the problem one, place the cursor of an the EV in the Ev() function you want to examine, and go Alt-I-F. Although this is strictly Insert Function, all it does in this case is bring up Excel's function wizard so that you can check out the inputs and see what the function evaluates to. If it doesn't evaluate to anything or it is an error, then you have found the problem.
Examples
  • Preview of the Ev example.
  • The above spreadsheet can be accessed from the Quick Reference (on the Business Functions menu), on the Examples menu, or in the Business Functions\Component Examples directory