What does the unary plus operator do in Excel formulas?

Question

This seemingly trivial operation can be useful in a number of situations within 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)

C. 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 on this - perhaps this is a new finding.

This question is in part for interests' sake and in part to see if anyone can shed further light or find other possible applications - excel or vba related?

Answer 1

In some of those examples, e.g. for WORKDAY and QUOTIENT, the + is converting a range to an array

Many of the old Analysis ToolPak functions like WORKDAY, NETWORKDAYS, WEEKNUM etc. won't accept a range as an argument, but in Excel 2007 or later excel versions they will accept an array as an argument - using +0 or -- (or apparently +) will convert the range to an array, so in Excel 2007 if you use a formula like this:

=AVERAGE(WEEKNUM(A1:A3))

where A1:A3 contain dates

....it will return an error

but this version (array entered with CTRL+SHIFT+ENTER) will work to give you the average of the week numbers:

=AVERAGE(WEEKNUM(+A1:A3))

Answer 2

Interesting question. It is clearly some sort of behind the scenes type conversion. I haven't tested your example in C, but it is interesting to note that in the first two cases the unary + can be replaced by either + 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 to why exactly this happens -- I don't know. Some functions seem to pass their arguments to Excel as strings when you use them in an array formula and adding 0 (which is what the unary plus does) coerces them to numbers. In VBA I don't think that this trick should be used since it would lead to obscure code, but it is useful to know for array formulas.

Answer 3

Various tests confirm the range to array explanation put forward by Barry Houdini. I'm including some results from the VBE watch window here for reference:

The watch window returns valuable details of the data types returned which are not available in the normal formula evaluation window. Note that [+A1:A3] returns a variant array whereas [A1:A3] returns a range reference. This method also shows that OFFSET and INDIRECT with array argument return arrays of range references. To force array evaluation you can wrap the formula in TRANSPOSE.

As a general technique copy the formula to the immediate window and enclose in square brackets then select the formula in the immediate window and drag to the watch window. No separate code is generally required for this but the VBA Evaluate method (or [] shortcut) doesn't like CELL or INDIRECT. A workaround for examining such formulas is to use the code below pasted into a 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