3

I'm trying to come up with a LAMBDA formula that captures the following recursive calculation:

Column A has 40 rows with integers between 1 and 40. Column B divides each integer in column A by 6 and rounds it up. Column C divides each integer in column B by 6 and rounds it up. This continues until the integer is 1 or less, and then I want the sum of the full row for a given integer. So, for example, for the number 25 in column A, I get 6 (5 from column B and 1 from column C). For the number 40 in column A, I get 10 (7 from column B, 2 from column C, 1 from column D).

Is it possible to come up with a LAMBDA function that would get me the correct output for a given number in column A? I don't want to use VBA - just want to use the LAMBDA function for this.

Image of the XL

Data Column 1 Column 2 Column 3 Column 4 Sum
1 0 0 0 0 1
2 1 0 0 0 1
3 1 0 0 0 1
4 1 0 0 0 1
5 1 0 0 0 1
6 1 0 0 0 1
7 2 1 0 0 3
8 2 1 0 0 3
9 2 1 0 0 3
10 2 1 0 0 3
11 2 1 0 0 3
12 2 1 0 0 3
13 3 1 0 0 4
14 3 1 0 0 4
15 3 1 0 0 4
16 3 1 0 0 4
17 3 1 0 0 4
18 3 1 0 0 4
19 4 1 0 0 5
20 4 1 0 0 5
21 4 1 0 0 5
22 4 1 0 0 5
23 4 1 0 0 5
24 4 1 0 0 5
25 5 1 0 0 6
26 5 1 0 0 6
27 5 1 0 0 6
28 5 1 0 0 6
29 5 1 0 0 6
30 5 1 0 0 6
31 6 1 0 0 7
32 6 1 0 0 7
33 6 1 0 0 7
34 6 1 0 0 7
35 6 1 0 0 7
36 6 1 0 0 7
37 7 2 1 0 10
Improve this question
1

2 Answers 2

Reset to default
3

Use BYROW and SCAN:

=BYROW(A1:A40,LAMBDA(c,SUM(SCAN(c,SEQUENCE(,4,6,0),LAMBDA(a,b,IF(a=1,0,ROUNDUP(a/b,0)))))))

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks! I've played around with something else here - it seems to work but I'm not sure if its valid for higher numbers: =LAMBDA(Input,IF(Input<2,0,ROUNDUP(Input/6,0)+CUSTOMFUNCTIONNAME(ROUNDUP(Input/6,0))) - any thoughts?
your question specifically stated that 40 was the range. For larger numbers, you will need to adjust the SEQUENCE(,4,6,0) Change the 4 to something that will encase the most iteration you think you will need. And change the 6 to the divisor you want.
Thanks - I don't want to have to "guess" the most iterations I will need to make, so I think this formula simplifies it a bit (the reason I chose 0 for anything less than 2 is arbitrary but hopefully the recursion logic holds): =LAMBDA(Input,IF(Input<2,0,ROUNDUP(Input/6,0)+CUSTOMFUNCTIONNAME(ROUNDUP(Input/6,0)))
@sts 1. I doubt even if you put 100 in the first number of the sequence it would be detrimental. 2. I hate using the name manager in this fashion, especially after the advent of SCAN, BYROW and others, but that is a personal opinion. 3. The main difference is that I get to enter one formula for the whole list. 4. But, if it works for you great, consider creating your own answer to this question. Your method may appeal to others in the future.
0

Here is a true recursive function/formula:

=LET(
    x, ROUNDUP(A2 / 6, 0),
    f, LAMBDA(ME,n, IF(n = 1, n, n + ME(ME, ROUNDUP(n / 6, 0)))),
    f(f, x)
)

This is a dynamic version of same formula:

=MAP(
    A2:A41,
    LAMBDA(m, LET(x, ROUNDUP(m / 6, 0), f, LAMBDA(ME,n, IF(n = 1, n, n + ME(ME, ROUNDUP(n / 6, 0)))), f(f, x)))
)

enter image description here

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.