Fresh new study regarding large numbers of operations. Initially inspired by
Summary of my custom LAMBDAs for Large Numbers • Excel Ticker , this is my take on the matter.
My functions will cover the following operations,
addition (positive numbers),
subtraction,
summing, (any numbers),
multiplication,
sumproduct,
max,
min,
comparison
operators, and not only for 2 numbers, we can perform these operations for entire huge arrays with very large numbers, single cell functions, no need for helper array spreadsheet constructions, do not call any other custom-made functions apart from a recursive one for addition and one for subtraction, do not even need the new lambda helper functions.
The challenge was to find a new super recursive efficient and fast concept that will
never reach recursion limits.
To accomplish this, the new concept does not iterate by the numbers of rows or by the numbers of digits, which will imply many iterations and will run out of recursion resources quite fast. The new concept is able to handle arrays of
hundreds of thousands of rows with numbers that can have
thousands of digits with
less than 10 recursive iterations. I have even built tools that count the number of iterations to prove that.
The only limit I have seen was the classic "Excel run out of resources...." message,
never a #NUM error.
For example, these are the limits when it comes to adding large numbers. If we increase by 1 any of shown values will get the out of resources message.
First column represents max nr. of digits a number can have, second column represents nr. of rows (nr. of number values).
On third column we have values returned by a function I got with the help of charts trendlines functions. I chose a function that is quite accurate for values that are not close to the extremes,
y=5E+07*x^-0.994 where x: nr. of digits; y: nr. of rows (number values)
digits | rows | function estimation | | | | | | | | | |
---|
40 | exceeds excel nr. rows | 1277975 | | | | | | | | | |
50 | 1048571 | 1023750 | | | | | | | | | |
100 | 536870 | 514008 | | | | | | | | | |
200 | 268435 | 258075 | | | | | | | | | |
500 | 107374 | 103799 | | | | | | | | | |
1000 | 53687 | 52116 | | | | | | | | | |
20000 | 2684 | 2653 | | | | | | | | | |
32766 | 10 | 1624 | | | | | | | | | |
40000 | exceeds nr. chars in a cell | 1332 | | | | | | | | | |
As we see, the numbers with 40 digits we can add will exceed excel rows real estate 1,048,576, that's why I was not able to get an accurate value with my tests. Same for nr. of chars a cell can take, 32,767.
And if you think that these numbers are big, please check this amazing instructive and funny YT:
Neil deGrasse Tyson Explains Big Numbers
Chapter 1. ADDition
ALADD(a) Array
Large numbers
ADDition. Calls recursive tool function T_LA
a: vertical array of pozitive numbers (for 2D arrays we can use ALADD(AFLAT(a)) )
Note: numbers can be in general number format or, for numbers that exceed 15 digits, in string format, to avoid Excel changing to scientific (exponential) notation.
Even if scientific notation is a format façade, if we introduce a number that has 16 "9"'s , the last digit will be turned into a "0", compromising the accuracy of the results.
Excel Formula:
=LAMBDA(a,
LET(l,LEN(a),m,MAX(l),
u,MMULT(SEQUENCE(,ROWS(a))^0,--MID(REPLACE(a,1,0,REPT(0,m-l)),SEQUENCE(,m),1)),
d,T_LA(u),
CONCAT(FILTER(d,SEQUENCE(,COLUMNS(d))>=XMATCH(TRUE,d<>0)))
)
)
T_LA(a) !!recursive!!
a: 1 row array of numbers ("a" is delivered to T_LA by first part of ALADD calculations)
Excel Formula:
=LAMBDA(a,
LET(c,COLUMNS(a),l,LEN(a),m,MAX(l),r,SEQUENCE(m),s,SEQUENCE(,c+m-1)-r+1,
u,MMULT(SEQUENCE(,m)^0,--IFERROR(INDEX(MID(REPLACE(a,1,0,REPT(0,m-l)),r+SEQUENCE(,c)^0-1,1),r,IF(s<1,-1,s)),0)),
IF(AND(LEN(u)=1),u,T_LA(u))
)
)
LARGE NR OP.xlsx |
---|
|
---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE |
---|
1 | Chapter 1. ADDition. Part 1. Concept | | | | | | | | | steps | | | | | | | | | | | | | | | | |
---|
2 | | | | | | | | | | | | | | | 1. | summing by col | | | | | | | | | | | | | |
---|
3 | | | insterting leading 0's and splitting | | | | | 81 | 99 | 117 | 126 | 126 | 126 | 126 | 126 | 126 | 126 | 126 | | | | | |
---|
4 | 99999999999 | | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | | | | | | | | | | | | | | | | | | |
---|
5 | 9999999999 | | 0 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | | 2. | inserting leading 0's | | | | | | | | | | | | |
---|
6 | 999999999 | | 0 | 0 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | | | 081 | 099 | 117 | 126 | 126 | 126 | 126 | 126 | 126 | 126 | 126 | | | | | |
---|
7 | 99999999999 | | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | | | | | | | | | | | | | | | | | | |
---|
8 | 99999999999 | | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | | 3. | extracting 1st digits as 1st row, 2nd digits as 2nd row + offsetting 1 col to the right, etc… | |
---|
9 | 99999999999 | | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | | | (done more or less manually to demonstrate the concept) | | | | | | |
---|
10 | 99999999999 | | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | | | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | | | | |
---|
11 | 9999999999 | | 0 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | | | | 8 | 9 | 1 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | | | | |
---|
12 | 999999999 | | 0 | 0 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | | | | | 1 | 9 | 7 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | | | |
---|
13 | 99999999 | | 0 | 0 | 0 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | | | | | | | | | | | | | | | | | | |
---|
14 | 99999999999 | | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | | 4. | summing bycol again (BYCOL can be used but I have used MMULT instead across all functions) | |
---|
15 | 99999999999 | | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | | | 0 | 8 | 11 | 11 | 10 | 9 | 9 | 9 | 9 | 9 | 9 | 8 | 6 | | | |
---|
16 | 99999999999 | | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | | | | | | | | | | | | | | | | | | |
---|
17 | 99999999999 | | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | | 5. | if the row array from step 4. has more than one digit we loop the process from 2. | |
---|
18 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
---|
19 | | | | | | | | | | | | | | | 2.1 | 00 | 08 | 11 | 11 | 10 | 09 | 09 | 09 | 09 | 09 | 09 | 08 | 06 | | | |
---|
20 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
---|
21 | | | | | | | | | | | | | | | 3.1 | 0 | 0 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | |
---|
22 | | | | | | | | | | | | | | | | | 0 | 8 | 1 | 1 | 0 | 9 | 9 | 9 | 9 | 9 | 9 | 8 | 6 | | |
---|
23 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
---|
24 | | | | | | | | | | | | | | | 4.1 | 0 | 0 | 9 | 2 | 2 | 0 | 9 | 9 | 9 | 9 | 9 | 9 | 8 | 6 | | |
---|
25 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
---|
26 | | | | | | | | | | | | | | | 5.1 | row array 4.1, this time, only 1 digit=> we exit the loop | | | | | | |
---|
27 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
---|
28 | | | | | | | | | | | | | | | 6. | concat row array from 4.1 ignoring first 0's, this will be the sum result | | | | |
---|
29 | | | | | | | | | | | | | | | | 922099999986 | | | | | | | | | | | | | |
---|
30 | check | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
---|
31 | =ALADD(A4:A17) | | | | | | | | | | | | | | | =SUM(A4:A17)&"" | | | | | | | | | | | | |
---|
32 | 922099999986 | | | | | | | | | | | | | | check | 922099999986 | had to use &"" trick to avoid exponantial format | | | | | |
---|
33 | for accurate results we always have to work | | | | | | | | | =SUM(A4:A17) | | | | | | | | | | | | | |
---|
34 | with string representations of numbers | | | | | | | | | 9.221E+11 | | | | | | | | | | | | | |
---|
35 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
---|
|
---|