2

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
ZygD
  • 22,092
  • 39
  • 79
  • 102
STS
  • 21
  • 2
  • Please provide sample data [repro] - using [Markdown Table Generator](https://www.tablesgenerator.com/markdown_tables) and a table showing your expected result. It is much easier to help you then. – Ike Jul 26 '22 at 13:01

1 Answers1

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

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • 1
    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? – STS Jul 26 '22 at 14:18
  • 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. – Scott Craner Jul 26 '22 at 14:20
  • 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 Jul 26 '22 at 14:23
  • @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. – Scott Craner Jul 26 '22 at 16:52