What does the unary plus operator do in Excel formulas?

Question

This seemingly trivial operation can be useful in a number of situations inside formulas:

A. functions that would otherwise throw an error:

QUOTIENT(+A1:A3,4) WORKDAY(+A1:A3,7) 

B. converting a range to numbers - i.e. any text to zero:

 N(+A1:C3) 

S returning an array of mixed data from different sheets:

 CELL("contents",IF(1,+INDIRECT({"Sheet1!A1","Sheet2!B2","Sheet3!C3"}))) 

I have found very little about this - perhaps this is a new conclusion.

This question partly depends on interests and partly in order to find out if someone can shed some light or find other possible applications - related to excel or vba?

Answer 1

In some of these examples, for example for WORKDAY and QUOTIENT + converts the range to an array

Many of the old Analysis ToolPak functions like WORKDAY , NETWORKDAYS , WEEKNUM , etc. will not accept a range as an argument, but in Excel 2007 or later versions, they will accept an array as an argument. Using +0 or -- (or, obviously, +) converts the range to an array, so in Excel 2007, if you use this formula:

=AVERAGE(WEEKNUM(A1:A3))

where A1: A3 contains dates

.... he will return an error

but this version (an array entered using CTRL + SHIFT + ENTER ) will work to give you the average number of weeks:

=AVERAGE(WEEKNUM(+A1:A3))

Answer 2

Interest Ask. Obviously, this is a bit of a backstage transformation. I have not tested your example in C, but it is interesting to note that in the first two cases, unary + can be replaced with + 0 or Value ():

 QUOTIENT(A1:A3 + 0,4) WORKDAY(A1:A3 + 0,7) N(A1:C3 + 0) 

or

 QUOTIENT(Value(A1:A3),4) WORKDAY(Value(A1:A3),7) N(Value(A1:C3)) 

As for why this is happening, I don’t know. Some functions seem to pass their arguments to Excel as strings, when you use them in an array formula and add 0 (which is what a unary plus does), coerces them into numbers. In VBA, I don't think this trick should be used, as it will lead to obscure code, but it's useful to know the array formulas.

Answer 3

Various tests confirm that the array argument presented by Barry Houdini points to a range. I am including some results from the VBE viewport here for reference:

The clock window returns valuable information about the returned data types that are not available in the normal formula evaluation window. Note that [+A1:A3] returns an array of options, while [A1:A3] returns a range reference. This method also shows that OFFSET and INDIRECT with an array of arguments return arrays of range references. To force an evaluation of an array, you can wrap the formula in TRANSPOSE .

As a general method, copy the formula into the immediate window and enclose in square brackets, then select the formula in the direct window and drag it into the viewport. This usually does not require separate code, but the VBA Evaluate (or [] shortcut) method is not like CELL or INDIRECT . A workaround for learning these formulas is to use the code inserted in the new module:

 Dim v Function eval(formula As String) v = Empty With Application .Goto "SetV(" & .ConvertFormula(formula, xlA1, xlR1C1) & ")" End With eval = v End Function Public Function SetV(Value) As Range v = Value Set SetV = Selection End Function