How does "Reduce formula" start caclulation without a given initial value (excel 365)

Question

Let's see what microsoft says:

https://support.microsoft.com/en-us/office/reduce-function-42e39910-b345-45f3-84b8-0642b568b7cb

It says:

Reduce function reduces an array to an accumulated value by applying a LAMBDA to each value and returning the total value in the accumulator.

I've done some practice using Reduce formula and feel confused when initial value is not specified.

I first try a simple example

This example tells me that if initial value is not specified,then the first accumulator value is blank.

Then I tried another:

This formula calculats 4 times which corresponds to the number of array elements (i.e 100,101,102,103).

So we get a 4 stacks of E3:G5 and initial value 200 at the bottom.

Everything is understandable until I tried to remove inital value:

My questions are:

1.In example 2,v (100,101,102,103) is not shown in the result array.
How does the result array of example3 include a '100' at the bottom?

Formula in LAMBDA (ie. VSTACK(E3:G5,a)) doesn't even include 'v'.
Does accumulator 'a' take the 1st element from the array as its value if initial value is not specified?

2.Result array only have 3 stacks of E3:G5. Why?
My guess is that calculation only starts to do what it have to do (vstack) when accumulator 'a' is not null. So it 'consumes' one calculation in order to take 100 to be its initial value. Is my guess correct?

Answer 1

If you do not specify an initial value in REDUCE and SCAN, the first value in the second argument array will be a and the second value will be v. If you specify an initial value, initialvalue will be a and the first value in the array of the second argument will be v.

What is important here is that the initial value a is output directly to the function defined as lambda without being evaluated.

This equally applies to REDUCE used in JavaScript and Python.

Answer 2

The REDUCE function exhibits some subtle behaviours.

Consider, the following pair whose difference is the absence/presence of an initial_value argument in the REDUCE function.

=REDUCE(,SEQUENCE(4,,100),LAMBDA(a,v,a+v)) and
=REDUCE(0,SEQUENCE(4,,100),LAMBDA(a,v,a+v))

both provide the same result of 406 and act as a simple accumulator of the elements in the array SEQUENCE(4,,100).

However, now consider

=REDUCE(,SEQUENCE(4,,100),LAMBDA(a,v,a+1)) and
=REDUCE(0,SEQUENCE(4,,100),LAMBDA(a,v,a+1))

These provide different results: the former is 103 whilst the latter is 4. In both cases REDUCE iterates over the 4 array elements of the array SEQUENCE(4,100) but the difference between the two cases lies in the iteration result of the first element: this is 100 (the first element of the array) when no initial-value argument is supplied to REDUCE and 1 when it is explicitly set to zero. If the purpose of this particular pair of formulae is to count the number of items within the array SEQUENCE(4,,100), then the second result is clearly the one that is desired.

One hypothesis is that the difference in the second pair arises because the third argument of the LAMBDA function no longer explicitly refers to the same function's second argument, v. However, this hypothesis is false since in the pair

=REDUCE(,SEQUENCE(4,,100),LAMBDA(a,v,a+(v/v))) and
=REDUCE(0,SEQUENCE(4,,100),LAMBDA(a,v,a+(v/v)))

there is such an explicit reference but the same results as the second pair are still obtained.

EDIT

The rules can be "squared" by noting that for the first pair, the result of the iterative calculation corresponding to the first element of the sequence is the same in both cases. Without an initial_value it is simply the first element of the sequence ie 100. With the initial value it is the result a+v where a is the initial_value of 0 and v is the first element of 100.

Answer 3

I posted a link to your question to the LinkedIn Excel Lambda discussions group.

Diarmuid Early of @DimEarly on YouTube has this to say:

If there's no initial value, REDUCE takes the first element of the array as the initial value, then iterates over the remaining elements of the array. If this results in an empty array (i.e. there was only one value to start with), it just returns that one value.

Interestingly, the function write-up not only doesn't explain this, put provides an actively misleading example. Example 1 in the article below omits the 'initial value' argument, but it only works by coincidence because 1 = 1^2 (if you try it and put in any other value, it gives the wrong answer).

https://support.microsoft.com/en-us/office/reduce-function-42e39910-b345-45f3-84b8-0642b568b7cb?ns=excel&version=90&syslcid=1033&uilcid=1033&appver=zxl900&helpid=xlmain11.chm60702&ui=en-us&rs=en-us&ad=us