Recursive Lambda for Euler formula

[macfuller]

It's March Madness time. We assign points to teams and you get those points each time the team wins. Sometimes we assign all points (64...1) where your pick to win it all gets 64, the other finalist 63, etc..., sometimes just the top 20 or top 10. I want to calculate the total possible points for a player for all the rounds using Euler's formula and I'm trying to create a recursive Lambda but I keep getting errors.

Let's take top 10 only. So players get 10+9+8+7+6+5+4+3+2+1 = 55 points if all 10 teams win. Euler's formula is n/2 * (max + min) where n is the number of sequential values in the series. With 10 values we get 10/2 * (10+1) = 5 * 11 = 55 in Round 1.

However, there are 32 games being played in the first round, but I only need 10 for this calculation, so I want n to be 10 instead of 32. Once I get to the Sweet 16 I only have 8 games being played so n = 8 and my minimum value is 3 (chopping off the lowest two scores). Elite 8 has only 4 games being played so n=4 and I only want 10+9+8+7. The total possible for points is 255

Round 1	55
Round 2	55
Round 3	52
Round 4	34
Round 5	19
Round 6	10
225

I've got this parameterized so that I get the right values for individual rounds, but I get #CALC error for my Lambda named EulerSum. I'm trying to call it recursively for 64 teams, then 32, then 16 etc until I'm down to one. I appreciate your help.
Inputs are: MaxPt = 10, MinPt = 1, Iteration = 64, Total = 0

= LAMBDA(
    MaxPt,
    MinPt,
    Iteration,
    Total,
    IF(Iteration = 1,
       Total + MaxPt,
         Total
       + EulerSum(
           MaxPt,
           MinPt,
           Iteration,
           Total,
           LET(
             Iteration,
             Iteration / 2,
             PtCount,
             MIN(Iteration, MaxPt),
             LowPt,
             IF(Iteration > MaxPt, MinPt, MaxPt - Iteration + 1),
             Euler,
             (PtCount / 2) * (MaxPt + LowPt),
             Euler))))

 

[coulombevin]

Hi @macfuller,

Lambda propose to wrap optional arguments with [brackets] and use if ISOMITTED:

=LAMBDA(MaxPt,MinPt,Iteration,[Total], IF(Iteration = 1,
IF(ISOMITTED(Total),0,Total),
LET(nextIteration,Iteration/2,
PtCount,MIN(nextIteration, MaxPt),
LowPt,IF(nextIteration > MaxPt, MinPt, MaxPt - nextIteration + 1),
Euler,(PtCount / 2) * (MaxPt + LowPt),
EulerSum(MaxPt,MinPt,nextIteration,IF(ISOMITTED(Total),0,Total)+Euler))))

This way you can call the function without providing the total (by default 0).

Bests regards,

Vincent

 

[macfuller]

What I meant was, is it possible to track the running total without introducing an outside variable at all? If the function initializes a variable within, then it's going to be reset each time the recursion is called, so I didn't know if there's a way to eliminate that external variable entirely.

 

[coulombevin]

What I meant was, is it possible to track the running total without introducing an outside variable at all? If the function initializes a variable within, then it's going to be reset each time the recursion is called, so I didn't know if there's a way to eliminate that external variable entirely.

That would be possible using this:

=LAMBDA(MaxPt,MinPt,Iteration, IF(Iteration = 1,0,    
LET(nextIteration,Iteration/2,
PtCount,MIN(nextIteration, MaxPt),
LowPt,IF(nextIteration > MaxPt, MinPt, MaxPt - nextIteration + 1),
Euler,(PtCount / 2) * (MaxPt + LowPt),
EulerSum(MaxPt,MinPt,nextIteration)+Euler)))

Bests regards,

Vincent