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

 

[Eric W]

I haven't checked the logic of your function, but are you calling it Euler or EulerSum? You have both names in the LAMBDA. Also, when you call the function Euler twice near the end, you aren't passing any parameters.

 

[macfuller]

Function is named EulerSum. My last LET variable is Euler where I execute the Euler formula, then I name the Euler variable as the return value for the LET. A bit redundant in the LET I know, but that’s my habit.

 

[coulombevin]

Hi @macfuller ,

Here's an alternative that seems simpler than recursive calling:

Classeur1 (version 1).xlsm
	A	B
1	Round 1	55
2	Round 2	55
3	Round 3	52
4	Round 4	34
5	Round 5	19
6	Round 6	10
7	225
Feuil5

Cell Formulas
Range	Formula
A1:B7	A1	=LAMBDA(nbTeams,[maxPoints],LET( rounds,LOG(nbTeams,2), maxPts,IF(ISOMITTED(maxPoints),10,IFERROR(IF(AND(ISNUMBER(maxPoints),maxPoints>0),maxPoints,10),10)), tbl,MAKEARRAY(rounds,2,LAMBDA(r,c,IF(c=1,"Round " & r,SUM(SEQUENCE(IF(((2^(rounds-r+1))/2)>= maxPts,maxPts,(2^(rounds-r+1))/2),1,maxPts,-1))))), VSTACK(tbl,MAKEARRAY(1,2,LAMBDA(r,c,IF(c=1,SUM(CHOOSECOLS(tbl,2)),""))))))(64)
Dynamic array formulas.

** How does it work **

The formula (that you can call with the name you want) only ask for the number of teams. If you whant to change points, you can give max value (10 is the default):

=LAMBDA(nbTeams,[maxPoints],LET(rounds,LOG(nbTeams,2),
maxPts,IF(ISOMITTED(maxPoints),10,IFERROR(IF(AND(ISNUMBER(maxPoints),maxPoints>0),maxPoints,10),10)),
tbl,MAKEARRAY(rounds,2,LAMBDA(r,c,IF(c=1,"Round " & r,SUM(SEQUENCE(IF(((2^(rounds-r+1))/2)>= maxPts,maxPts,(2^(rounds-r+1))/2),1,maxPts,-1))))),
VSTACK(tbl,MAKEARRAY(1,2,LAMBDA(r,c,IF(c=1,SUM(CHOOSECOLS(tbl,2)),""))))))

The output will be a table with 2 column. The first column contain "Round x", except for the last row which is the sum of all rows in column 2, and the second one the maximum points.

** ERROR **
You might get a propag error if there is someting on the way of the generated table (2 colum x LOG2 nbTeams rows).

Here's the french version:

=LAMBDA(nbTeams;[maxPoints];LET(
rounds;LOG(nbTeams;2);
maxPts;SI(ISOMITTED(maxPoints);10;SIERREUR(SI(ET(ESTNUM(maxPoints);maxPoints>0);maxPoints;10);10));
tbl;MAKEARRAY(rounds;2;LAMBDA(r;c;SI(c=1;"Round " & r;SOMME(SEQUENCE(SI(((2^(rounds-r+1))/2)>= maxPts;maxPts;(2^(rounds-r+1))/2);1;maxPts;-1)))));
ASSEMB.V(tbl;MAKEARRAY(1;2;LAMBDA(r;c;SI(c=1;SOMME(CHOISIRCOLS(tbl;2));""))))))(64)

Bests regards,

Vincent

 

[coulombevin]

Hi @macfuller,

Sorry for my previous message, I forgot to prevent odd numbers. Here's a corrected version with roundup (6.01 rounds would trow an error in makearray with 6.01 rows):

Classeur1 (version 1).xlsm
	A	B
1	Round 1	55
2	Round 2	55
3	Round 3	52
4	Round 4	34
5	Round 5	19
6	Round 6	10
7	225
Feuil5

Cell Formulas
Range	Formula
A1:B7	A1	=LAMBDA(nbTeams,[maxPoints],LET( rounds,ROUNDUP(LOG(nbTeams,2),0), maxPts,IF(ISOMITTED(maxPoints),10,IFERROR(IF(AND(ISNUMBER(maxPoints),maxPoints>0),maxPoints,10),10)), tbl,MAKEARRAY(rounds,2,LAMBDA(r,c,IF(c=1,"Round " & r,SUM(SEQUENCE(IF(((2^(rounds-r+1))/2)>= maxPts,maxPts,(2^(rounds-r+1))/2),1,maxPts,-1))))), VSTACK(tbl,MAKEARRAY(1,2,LAMBDA(r,c,IF(c=1,SUM(CHOOSECOLS(tbl,2)),""))))))(64)
Dynamic array formulas.

=LAMBDA(nbTeams,[maxPoints],LET(rounds,ROUNDUP(LOG(nbTeams,2),0),
maxPts,IF(ISOMITTED(maxPoints),10,IFERROR(IF(AND(ISNUMBER(maxPoints),maxPoints>0),maxPoints,10),10)),
tbl,MAKEARRAY(rounds,2,LAMBDA(r,c,IF(c=1,"Round " & r,SUM(SEQUENCE(IF(((2^(rounds-r+1))/2)>= maxPts,maxPts,(2^(rounds-r+1))/2),1,maxPts,-1))))),
VSTACK(tbl,MAKEARRAY(1,2,LAMBDA(r,c,IF(c=1,SUM(CHOOSECOLS(tbl,2)),""))))))

in french:

=LAMBDA(nbTeams;[maxPoints];LET(
rounds;ARRONDI.SUP(LOG(nbTeams;2);0);
maxPts;SI(ISOMITTED(maxPoints);10;SIERREUR(SI(ET(ESTNUM(maxPoints);maxPoints>0);maxPoints;10);10));
tbl;MAKEARRAY(rounds;2;LAMBDA(r;c;SI(c=1;"Round " & r;SOMME(SEQUENCE(SI(((2^(rounds-r+1))/2)>= maxPts;maxPts;(2^(rounds-r+1))/2);1;maxPts;-1)))));
ASSEMB.V(tbl;MAKEARRAY(1;2;LAMBDA(r;c;SI(c=1;SOMME(CHOISIRCOLS(tbl;2));""))))))

bests regards,

Vincent

 

[macfuller]

Thanks.
If I have separate rows for the max score in each row round I can have much simpler formulas for each round.
=(MIN(C78,C76)/2)*(C76+IF(C78>C76,C77,C76-C78+1))
Where C78 is the Iteration (64,32…), C76 is 10, C77 is 1.
I really want to understand how to properly call the recursive LAMBDA.

 

[coulombevin]

Hi @macfuller ,

If we realy want to use the formula you provided, you must modify it. Your lambda doesn't seems to make sens, it has 4 arguments: MaxPt;MinPt;Iteration;Total and later you try to call it recursively by forcing a let as a 5th argument inexistant. Without data, I can't wrap my head around and find a solution. Could you provide a sample of input?

Bests regards,

Vincent

 

[macfuller]

Thanks. You’re hitting on the key point… why is the LET statement a 5th parameter rather than the action of the EulerSum formula?

 

[coulombevin]

Hi @macfuller,

It is only a question of order. Here's the corrected version:

=LAMBDA(MaxPt,MinPt,Iteration,Total,  
IF(Iteration = 1, 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,Total+Euler))))

If whe are at the last iteration, return total and stop recursiveness. otherwise, find min, max, euler and call again this function using same min,max but half iteration and previous total + active euler value.

Bests regards,

Vincent

 

[macfuller]

By the way, do I need to submit the Total variable? I wasn't sure how to keep the running total without it, but didn't know how to make the equation return the total without it.