Lazy caterer's sequence

Hi all,

I'm trying to reverse the lazy caterer's sequence to determine which level of a number is closest.
1 1 1
2 2 3
3 4 7
4 7 14
5 11 25
6 16 41
7 22 63
8 29 92
9 37 129
10 46 175
56 231

EG:
175 is level 9 but what is the formula to return the number 9? (And yes the previous "cost" is required to get to that level (129 + 46 = 175))

I'd love to be able to get this into a single formula without the need for numbers in other rows etc. But I'm not sure that's possible.

The end goal is to have a number say 182 and return or tell us the MAXIMUM level you could get to. (9) And the remainder on a different cell. (not as important)

Assuming that your sequence is in columns A, B and C starting in row 1, and that you put the number 182 in cell E1, then you can use these formulae in the cells stated:

F1: =MATCH(E1,C:C)-1

to give you the level, and:

G1: =E1-INDEX(C:C,F1+1)

to give you the remainder.

Incidentally, you have 11 missing from your final example line.

Hope this helps.

Pete

A simpler way of generating the sequence - put 1 in the cells A1:C1, then these formulae:

A2: =A1+1

B2: =A1+B1

C2: =C1+B2

then copy those 3 formulae down as far as you need.

Hope this helps.

Pete

Thanks everyone!

=LET(s,SEQUENCE(41,,0,1),c,(s^3+3*s^2+8*s+6)/6,m,LOOKUP($A1,c),CHOOSE({1,2,3},m,$A1-m,MATCH(m,c,0)-1))

Can you explain this so I can tweak it?

I'd like to be able to control how quickly the sequence accelerates (instead of +1 it could be +2). And also how to display the results separately if so desired.
Also how can I get excel to display the calculated sequence (after sequence + all the math).

Thanks!

Hi all,

With the help of WideBoyDixon, PeteUK, and AI I've been able to figure most of it out.

Simplified below is the version I'm currently working on, and will add the match function back later. For now this displays the list of terms from the lazy caterer's sequence (+1) then I am attempting to cumulatively add those together in a simplified way (avoiding complex math). However, currently this is displaying a #value result after calculating the indexes, and I cannot figure out what is going on there.

=LET(a,SEQUENCE(10,1,0,1), b,((a^2+a+2)/2)+1, c,SEQUENCE(10,1,1,1), d,SUM(INDEX(b,1):INDEX(b,c,1)),d)

Can someone please take a look and see where this is screwed up.

The result should be (a spill):
2 5 10 18 30 47 70 100 138 185

Thanks!

No AliGW, it has nothing to do with the lazy carterer's sequence. If you want to directly message me please do so instead of posting directly into the forums.

Hi all,

With the help of WideBoyDixon, PeteUK, and AI I've been able to figure most of it out.

Simplified below is the version I'm currently working on, and will add the match function back later. For now this displays the list of terms from the lazy caterer's sequence (+1) then I am attempting to cumulatively add those together in a simplified way (avoiding complex math). However, currently this is displaying a #value result after calculating the indexes, and I cannot figure out what is going on there.

=LET(a,SEQUENCE(10,1,0,1), b,((a^2+a+2)/2)+1, c,SEQUENCE(10,1,1,1), d,SUM(INDEX(b,1):INDEX(b,c,1)),d)

Can someone please take a look and see where this is screwed up?

The result should be (a spill):
2 5 10 18 30 47 70 100 138 185

Thanks!

Hi,

If I didn't misunderstand, please try..

with SCAN - LAMBDA

Formula:

Please Login or Register  to view this content.

or without LAMBDA (using MMULT)

Formula:

Please Login or Register  to view this content.

Holy smokes dude! You nailed it on the first try!! WOW

Can you explain how it works please? In case I need to adjust things.

This is what an AI (Chat GPT) told me:

"LET function: This function is used to define variables that will be used in subsequent calculations. It's a way to assign names to values or expressions.

a: This variable is assigned the result of SEQUENCE(1, 10, 0, 1), which generates an array of 10 numbers starting from 0 and incrementing by 1. So, a will hold the values [0, 1, 2, ..., 9].
b: This variable is assigned the result of the expression ((a^2 + a + 2) / 2) + 1, which calculates the corresponding values of the sequence described in your original post. This is essentially a mathematical expression to generate Sequence 1.

SCAN function: This function is used to perform a cumulative operation on an array, applying a given function at each step. In this case, it's used for element-wise addition.
SCAN(0, b, LAMBDA(x, y, x + y)): The SCAN function takes three arguments: an initial value (0 in this case), the array b, and a lambda function LAMBDA(x, y, x + y).

LAMBDA(x, y, x + y): This is a lambda function that takes two arguments, x and y, and returns their sum (x + y). In this context, it's the element-wise addition operation.

SCAN iterates over the array b, applying the lambda function to each element and accumulating the result. So, the result of SCAN will be an array where each element is the sum of all preceding elements in the array b.

The result of the SCAN function is the final array of element-wise cumulative sums, which corresponds to the desired output.
The formula uses these steps to generate the element-wise cumulative sum of Sequence 1 and outputs the correct result: 2, 5, 10, 18, 30, 47, 70, 100, 138, 185."


Looking at the calculation steps in excel it literally shows #VALUE then suddenly calculates the correct result and works.

In my own words, it seems like the scan is what is doing the iterative process of adding, and lambda is explaining what to do with the sequence array?

The comment is absolutely correct.
Cumulative totals are obtained with the SCAN function.