7

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?

Community
  • 1
  • 1
lori_m
  • 5,487
  • 1
  • 18
  • 29
  • Fascinating discovery! The ability to retain datatype via this method is certain to have useful applications indeed. – XOR LX Jun 29 '15 at 09:25
  • I first came across this at a formula challenge I posted at http://forum.chandoo.org/threads/formula-challenge-012-turn-a-mixed-2-d-array-into-a-1-d-array.11371/page-2#post-66561 ...see the formula from Sam Mathai Chacko at the top of the page. (He uses a - ) – jeffreyweir Jul 21 '15 at 20:41

3 Answers3

6

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))

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • 1
    This does seem to explain the general behaviour and it's good to have confirmation that it is the former ATP functions that require this sort of conversion. I added some testing results in a separate response in case anyone is interested. – lori_m Jul 01 '15 at 06:14
2

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:

enter image description here

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
lori_m
  • 5,487
  • 1
  • 18
  • 29
1

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.

John Coleman
  • 51,337
  • 7
  • 54
  • 119
  • If the ranges contain only numbers then the alternatives you provide return the same results. If there are text strings in the third formula range reference these alternatives will error where unary plus versions return zeroes. Results may also differ in the first two formulas for example numeric strings get converted by binary addition. – lori_m Jun 27 '15 at 19:10
  • Strange behavior. I think that barry houdini's answer nails it and deserves to be accepted -- though perhaps some mystery remains. – John Coleman Jun 27 '15 at 20:03