Last active
October 4, 2025 04:52
-
-
Save CalfordMath/16c26ce650336db73503a4cfa608dfa3 to your computer and use it in GitHub Desktop.
Helpful Lambda Functions for Math Worksheet Automation
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| // --- Workbook module --- | |
| // A file of name definitions of the form: | |
| // name = definition; | |
| //Gives just the slope through (xA, yA) and (xB, yB), | |
| //Optional: just numerator num_den = "num", just denominator num_den = "den", leave blank for full slope | |
| //Optional: decimal = True or False (false by default, true if you want the slope as a decimal) | |
| //Optional: reduced = True or False (true by default) | |
| slopeFromPoints = LAMBDA(xA, yA, xB, yB, [num_den], [decimal], [reduced], | |
| LET( | |
| num_den, IF(ISOMITTED(num_den), "", num_den), | |
| decimal, IF(ISOMITTED(decimal), FALSE, decimal), | |
| reduced, IF(ISOMITTED(reduced), TRUE, reduced), | |
| dx, xB - xA, | |
| dy, yB - yA, | |
| absDx, ABS(dx), | |
| absDy, ABS(dy), | |
| IF( | |
| dx = 0, | |
| IF(dy = 0, "Error: Same points", "x = " & xA), | |
| LET( | |
| overallSign, SIGN(dx) * SIGN(dy), | |
| g, IF(reduced, GCD(absDy, absDx), 1), | |
| slopeNum, absDy / g * overallSign, | |
| slopeDen, absDx / g, | |
| slopeStr, IF(slopeDen = 1, slopeNum, slopeNum & "/" & slopeDen), | |
| slopeDec, slopeNum / slopeDen, | |
| IFS( | |
| decimal, | |
| slopeDec, | |
| LOWER(num_den) = "num", | |
| slopeNum, | |
| LOWER(num_den) = "den", | |
| slopeDen, | |
| TRUE, | |
| slopeStr | |
| ) | |
| ) | |
| ) | |
| ) | |
| ); | |
| //Gives b value of y=ax+b through (xA, yA) and (xB, yB) | |
| //Optional: decimal = True or False (false by default) | |
| yInterceptFromPoints = LAMBDA(xA, yA, xB, yB, [decimal], | |
| LET( | |
| decimal, IF(ISOMITTED(decimal), FALSE, decimal), | |
| dx, xB - xA, | |
| dy, yB - yA, | |
| absDx, ABS(dx), | |
| absDy, ABS(dy), | |
| IF( | |
| dx = 0, | |
| IF(dy = 0, "Error: Same points", "x = " & xA), | |
| LET( | |
| overallSign, SIGN(dx) * SIGN(dy), | |
| g, GCD(absDy, absDx), | |
| slopeNum, absDy / g * overallSign, | |
| slopeDen, absDx / g, | |
| bNum, yA * slopeDen - slopeNum * xA, | |
| bDen, slopeDen, | |
| bOverallSign, SIGN(bNum) * SIGN(bDen), | |
| absBNum, ABS(bNum), | |
| absBDen, ABS(bDen), | |
| gb, GCD(absBNum, absBDen), | |
| bNumS, absBNum / gb * bOverallSign, | |
| bDenS, absBDen / gb, | |
| bStr, IF(bDenS = 1, bNumS, bNumS & "/" & bDenS), | |
| bDec, bNumS / bDenS, | |
| IFS(decimal, bDec, TRUE, bStr) | |
| ) | |
| ) | |
| ) | |
| ); | |
| //Gives y=ax+b through (xA, yA) and (xB, yB) | |
| slopeIntercept = LAMBDA(xA, yA, xB, yB, | |
| LET( | |
| dx, xB - xA, | |
| dy, yB - yA, | |
| absDx, ABS(dx), | |
| absDy, ABS(dy), | |
| IF( | |
| dx = 0, | |
| IF(dy = 0, "Error: Same points", "x = " & xA), | |
| LET( | |
| overallSign, SIGN(dx) * SIGN(dy), | |
| g, GCD(absDy, absDx), | |
| slopeNum, absDy / g * overallSign, | |
| slopeDen, absDx / g, | |
| slopeStr, IF( | |
| slopeDen = 1, | |
| IF(slopeNum = 1, "", IF(slopeNum = -1, "-", slopeNum)), | |
| slopeNum & "/" & slopeDen | |
| ), | |
| bNum, yA * slopeDen - slopeNum * xA, | |
| bDen, slopeDen, | |
| bOverallSign, SIGN(bNum) * SIGN(bDen), | |
| absBNum, ABS(bNum), | |
| absBDen, ABS(bDen), | |
| gb, GCD(absBNum, absBDen), | |
| bNumS, absBNum / gb * bOverallSign, | |
| bDenS, absBDen / gb, | |
| bStr, IF( | |
| bNumS = 0, | |
| "", | |
| IF(bDenS = 1, absBNum / gb, absBNum / gb & "/" & bDenS) | |
| ), | |
| bSign, IF(bNumS = 0, "", IF(bNumS > 0, " + ", " - ")), | |
| IF( | |
| slopeNum, | |
| "y = " & slopeStr & "x" & bSign & bStr, | |
| "y = " & IF(bSign = " - ", "-", "") & bStr | |
| ) | |
| ) | |
| ) | |
| ) | |
| ); | |
| //Gives Ax + By + C = 0 through (xA, yA) and (xB, yB). If you want Ax + By = C enter False for C_on_left | |
| //set 'reduce' to False if you want to leave a common factor across the equation, default "True" will reduce A, B, and C | |
| standardLine = LAMBDA(xA, yA, xB, yB, [C_on_left], [reduce], | |
| LET( | |
| reduce_it, IF(ISOMITTED(reduce), TRUE, reduce), | |
| cLeft, IF(ISOMITTED(C_on_left), FALSE, C_on_left), | |
| dx, xB - xA, | |
| dy, yB - yA, | |
| absDx, ABS(dx), | |
| absDy, ABS(dy), | |
| IF( | |
| dx = 0, | |
| IF( | |
| dy = 0, | |
| "Error: Same points", | |
| IF( | |
| cLeft, | |
| "x " & IF(xA < 0, " + " & ABS(xA), IF(xA > 0, " - " & xA, "")) & | |
| " = 0", | |
| "x = " & xA | |
| ) | |
| ), | |
| LET( | |
| A, dy, | |
| B, -dx, | |
| C, dy * xA - dx * yA, | |
| absA, ABS(A), | |
| absB, ABS(B), | |
| absC, ABS(C), | |
| g, IF(reduce_it, GCD(GCD(absA, absB), absC), 1), | |
| A_reduced, A / g, | |
| B_reduced, B / g, | |
| C_reduced, C / g, | |
| A_final, IF(A_reduced < 0, -A_reduced, A_reduced), | |
| B_final, IF(A_reduced < 0, -B_reduced, B_reduced), | |
| C_final, IF(A_reduced < 0, -C_reduced, C_reduced), | |
| IF( | |
| A_final = 0, | |
| LET( | |
| B_str, IF(ABS(B_final) = 1, "", ABS(B_final)), | |
| B_str & | |
| IF( | |
| cLeft, | |
| "y " & | |
| IF( | |
| C_final < 0, | |
| " + " & ABS(C_final), | |
| IF(C_final > 0, " - " & C_final, "") | |
| ) & " = 0", | |
| "y = " & C_final | |
| ) | |
| ), | |
| LET( | |
| A_str, IF(A_final = 1, "", A_final), | |
| B_str, IF(ABS(B_final) = 1, "", ABS(B_final)), | |
| B_sign, IF(B_final >= 0, " + ", " - "), | |
| IF( | |
| B_final = 0, | |
| A_str & | |
| IF( | |
| cLeft, | |
| "x " & | |
| IF( | |
| C_final < 0, | |
| " + " & ABS(C_final), | |
| IF(C_final > 0, " - " & C_final, "") | |
| ) & " = 0", | |
| "x = " & C_final | |
| ), | |
| A_str & "x" & B_sign & B_str & | |
| IF( | |
| cLeft, | |
| "y " & | |
| IF( | |
| C_final < 0, | |
| " + " & ABS(C_final), | |
| " - " & ABS(C_final) | |
| ) & " = 0", | |
| "y = " & C_final | |
| ) | |
| ) | |
| ) | |
| ) | |
| ) | |
| ) | |
| ) | |
| ); | |
| //Gives standard form quadratic Ax²+Bx+C from k(ax-r)(bx-s), where k is an optional common factor, defaulted to 1. Remember, for (x+4)(x-5) -> r=-4 and s=5 | |
| standardQuadratic = LAMBDA(aVal, rVal, bVal, sVal, [common_factor], | |
| LET( | |
| cf, IF(ISOMITTED(common_factor), 1, common_factor), | |
| a_2, aVal * bVal * cf, | |
| a_1, (aVal * -sVal + -rVal * bVal) * cf, | |
| a_0, (sVal * rVal) * cf, | |
| sign_a_1, IF(a_1 > 0, "+", "-"), | |
| sign_a_0, IF(a_0 > 0, "+", "-"), | |
| IF(a_2 = 0, "", IF(a_2 = 1, "x²", IF(a_2 = -1, "-x²", a_2 & "x²"))) & | |
| IF( | |
| a_1 = 0, | |
| "", | |
| IF( | |
| a_2 = 0, | |
| IF(a_1 = 1, "x", IF(a_1 = -1, "-x", a_1 & "x")), | |
| " " & sign_a_1 & " " & IF(ABS(a_1) = 1, "x", ABS(a_1) & "x") | |
| ) | |
| ) & | |
| IF( | |
| a_0 = 0, | |
| IF(NOT(AND(a_1 = 0, a_2 = 0)), "", a_0), | |
| IF(NOT(AND(a_1 = 0, a_2 = 0)), " ", "") & sign_a_0 & ABS(a_0) | |
| ) | |
| ) | |
| ); | |
| //Gives factored form quadratic k(ax-r)(bx-s), where k is an optional multiplier outside the brackets, defaulted to 1. Remember, for (x+4)(x-5) -> r=-4 and s=5 | |
| factoredQuadratic = LAMBDA(aVal, rVal, bVal, sVal, [k], [common_factor], | |
| LET( | |
| cf, IF(ISOMITTED(common_factor), FALSE, common_factor), | |
| gcf_aVal_rVal, GCD(ABS(aVal), ABS(rVal)) * SIGN(aVal), | |
| gcf_bVal_sVal, GCD(ABS(bVal), ABS(sVal)) * SIGN(bVal), | |
| k, IF( | |
| cf, | |
| IF(ISOMITTED(k), 1, k) * gcf_aVal_rVal * gcf_bVal_sVal, | |
| IF(ISOMITTED(k), 1, k) | |
| ), | |
| LET( | |
| aValue, IF(cf, aVal / gcf_aVal_rVal, aVal), | |
| rValue, IF(cf, rVal / gcf_aVal_rVal, rVal), | |
| bValue, IF(cf, bVal / gcf_bVal_sVal, bVal), | |
| sValue, IF(cf, sVal / gcf_bVal_sVal, sVal), | |
| IF( | |
| OR(aValue = 0, bValue = 0), | |
| "Error: Not quadratic input.", | |
| IF( | |
| AND(rValue = 0, sValue = 0), | |
| niceCoef(k * aValue * bValue) & "x²", | |
| IF( | |
| rValue = 0, | |
| niceCoef(k * aValue) & "x(" & niceCoef(bValue) & "x" & | |
| IF(sValue < 0, " + ", " - ") & ABS(sValue) & ")", | |
| IF( | |
| sValue = 0, | |
| niceCoef(k * bValue) & "x(" & niceCoef(aValue) & "x" & | |
| IF(rValue < 0, " + ", " - ") & ABS(rValue) & ")", | |
| niceCoef(k) & "(" & niceCoef(aValue) & "x" & | |
| IF(rValue < 0, " + ", " - ") & ABS(rValue) & ")(" & | |
| niceCoef(bValue) & "x" & IF(sValue < 0, " + ", " - ") & | |
| ABS(sValue) & ")" | |
| ) | |
| ) | |
| ) | |
| ) | |
| ) | |
| ) | |
| ); | |
| //Cleans up coefficients so you don't see -1x or 1x | |
| niceCoef = LAMBDA(coefficient, SWITCH(coefficient, -1, "-", 1, "", coefficient)); | |
| //generates a random number that is mutually prime to a given number (num_1) | |
| MutuallyPrime = LAMBDA(num_1, minval, maxval, [count], | |
| LET( | |
| count, IF(ISOMITTED(count), 0, count + 1), | |
| num_2, RANDBETWEEN(minval, maxval), | |
| IF( | |
| count > 100, | |
| "#NumError: Couldn't find a mutually prime number to " & num_1 & | |
| " between " & minval & " and " & maxval, | |
| IF( | |
| GCD(ABS(num_1), ABS(num_2)) <> 1, | |
| MutuallyPrime(num_1, minval, maxval), | |
| num_2 | |
| ) | |
| ) | |
| ) | |
| ); | |
| //Accepts array of values the generated value must be mutually prime with, either as a range, A1:D1, or explicitly {2,3,5} | |
| MultiMutuallyPrime = LAMBDA(num_array, minval, maxval, [count], | |
| LET( | |
| count, IF(ISOMITTED(count), 0, count + 1), | |
| num_2, RANDBETWEEN(minval, maxval), | |
| num_array, TOROW(num_array), | |
| // For small arrays, just check each element explicitly | |
| all_mutually_prime, AND( | |
| GCD(ABS(num_2), ABS(INDEX(num_array, 1, 1))) = 1, | |
| IF( | |
| COLUMNS(num_array) >= 2, | |
| GCD(ABS(num_2), ABS(INDEX(num_array, 1, 2))) = 1, | |
| TRUE | |
| ), | |
| IF( | |
| COLUMNS(num_array) >= 3, | |
| GCD(ABS(num_2), ABS(INDEX(num_array, 1, 3))) = 1, | |
| TRUE | |
| ), | |
| IF( | |
| COLUMNS(num_array) >= 4, | |
| GCD(ABS(num_2), ABS(INDEX(num_array, 1, 4))) = 1, | |
| TRUE | |
| ), | |
| IF( | |
| COLUMNS(num_array) >= 5, | |
| GCD(ABS(num_2), ABS(INDEX(num_array, 1, 5))) = 1, | |
| TRUE | |
| ), | |
| IF( | |
| COLUMNS(num_array) >= 6, | |
| GCD(ABS(num_2), ABS(INDEX(num_array, 1, 6))) = 1, | |
| TRUE | |
| ), | |
| IF( | |
| COLUMNS(num_array) >= 7, | |
| GCD(ABS(num_2), ABS(INDEX(num_array, 1, 7))) = 1, | |
| TRUE | |
| ), | |
| IF( | |
| COLUMNS(num_array) >= 8, | |
| GCD(ABS(num_2), ABS(INDEX(num_array, 1, 8))) = 1, | |
| TRUE | |
| ), | |
| IF( | |
| COLUMNS(num_array) >= 9, | |
| GCD(ABS(num_2), ABS(INDEX(num_array, 1, 9))) = 1, | |
| TRUE | |
| ), | |
| IF( | |
| COLUMNS(num_array) >= 10, | |
| GCD(ABS(num_2), ABS(INDEX(num_array, 1, 10))) = 1, | |
| TRUE | |
| ) | |
| ), | |
| IF( | |
| count > 100, | |
| "#NumError: Couldn't find a mutually prime number to all values in array between " & | |
| minval & " and " & maxval, | |
| IF( | |
| all_mutually_prime, | |
| num_2, | |
| MultiMutuallyPrime(num_array, minval, maxval, count) | |
| ) | |
| ) | |
| ) | |
| ); | |
| //Formats a number as a coefficient, handling cases of 1 and -1 | |
| Coef = LAMBDA(num, IFS(num = -1, "-", num = 1, "", ABS(num) <> 1, num)); | |
| //Generates a random number within the specified min and max range | |
| //but NOT equal to any value passed in the 'old' array. | |
| //Old can be a selection of cells or explicitly {1,4,-3}. | |
| //If abs_strict is True (default is True), it will not allow -2 if old contains 2. | |
| //If no number can be found within 100 tries, it returns #NumError | |
| DifferentNumber = LAMBDA(old, MinNum, MaxNum, [ZeroAllowed], [AbsoluteStrict], [Count], | |
| LET( | |
| zero, IF(ISOMITTED(ZeroAllowed), FALSE, ZeroAllowed), | |
| absolute_strict, IF(ISOMITTED(AbsoluteStrict), TRUE, AbsoluteStrict), | |
| Count, IF(ISOMITTED(Count), 1, Count), | |
| new, RANDBETWEEN(MinNum, MaxNum), | |
| findtotal, IF( | |
| absolute_strict, | |
| SUM(1 * (ABS(old) = ABS(new))), | |
| SUM(1 * (old = new)) | |
| ), | |
| IF( | |
| OR( | |
| AND(zero = FALSE, new = 0, Count < 100), | |
| AND(findtotal > 0, Count < 100) | |
| ), | |
| DifferentNumber(old, MinNum, MaxNum, zero, absolute_strict, Count + 1), | |
| IF(Count >= 100, "#NumError", new) | |
| ) | |
| ) | |
| ); | |
| //this will reduce square roots. Enter only the radicand (value in the root). Leave the optional factor parameter blank. | |
| ReduceRadical = LAMBDA(radicand, [factor], | |
| LET( | |
| factor, IF(ISOMITTED(factor), ROUNDUP(SQRT(radicand), 0), factor), | |
| newradicand, radicand / (POWER(factor, 2)), | |
| IF( | |
| factor = 1, | |
| "√(" & radicand & ")", | |
| IF( | |
| newradicand - INT(newradicand) = 0, | |
| factor & "√(" & newradicand & ")", | |
| ReduceRadical(radicand, factor - 1) | |
| ) | |
| ) | |
| ) | |
| ); | |
| //Accepts the primary angles 30, 60, 45, "pi/6", "pi/3", "pi/4", and "sin", "cos", or "tan" | |
| ExactTrigRatio = LAMBDA(angle, ratio, | |
| SWITCH( | |
| LOWER(ratio), | |
| "sin", SWITCH( | |
| angle, | |
| 30, "1/2", | |
| 60, "√(3)/2", | |
| 45, "1/√(2)", | |
| "pi/6", "1/2", | |
| "pi/3", "√(3)/2", | |
| "pi/4", "1/√(2)" | |
| ), | |
| "cos", SWITCH( | |
| angle, | |
| 30, "√(3)/2", | |
| 60, "1/2", | |
| 45, "1/√(2)", | |
| "pi/6", "√(3)/2", | |
| "pi/3", "1/2", | |
| "pi/4", "1/√(2)" | |
| ), | |
| "tan", SWITCH( | |
| angle, | |
| 30, "1/√(3)", | |
| 60, "√(3)", | |
| 45, "1", | |
| "pi/6", "1/√(3)", | |
| "pi/3", "√(3)", | |
| "pi/4", "1" | |
| ) | |
| ) | |
| ); | |
| //Breaks text up, putting each character in its own cell horizontally | |
| SplitChar = LAMBDA(text, MID(text, SEQUENCE(1, LEN(text), 1), 1)); | |
| // Converts numbers and +/- to superscript characters | |
| SuperScript = LAMBDA(text, | |
| LET( | |
| step1, SUBSTITUTE(text, "0", UNICHAR(8304)), | |
| step2, SUBSTITUTE(step1, "1", "¹"), | |
| step3, SUBSTITUTE(step2, "2", "²"), | |
| step4, SUBSTITUTE(step3, "3", "³"), | |
| step5, SUBSTITUTE(step4, "4", UNICHAR(8308)), | |
| step6, SUBSTITUTE(step5, "5", UNICHAR(8309)), | |
| step7, SUBSTITUTE(step6, "6", UNICHAR(8310)), | |
| step8, SUBSTITUTE(step7, "7", UNICHAR(8311)), | |
| step9, SUBSTITUTE(step8, "8", UNICHAR(8312)), | |
| step10, SUBSTITUTE(step9, "9", UNICHAR(8313)), | |
| step11, SUBSTITUTE(step10, "+", UNICHAR(8314)), | |
| step12, SUBSTITUTE(step11, "-", UNICHAR(8315)), | |
| step12 | |
| ) | |
| ); | |
| // Converts numbers and +/- to subscript characters | |
| SubScript = LAMBDA(text, | |
| LET( | |
| step1, SUBSTITUTE(text, "0", UNICHAR(8320)), | |
| step2, SUBSTITUTE(step1, "1", UNICHAR(8321)), | |
| step3, SUBSTITUTE(step2, "2", UNICHAR(8322)), | |
| step4, SUBSTITUTE(step3, "3", UNICHAR(8323)), | |
| step5, SUBSTITUTE(step4, "4", UNICHAR(8324)), | |
| step6, SUBSTITUTE(step5, "5", UNICHAR(8325)), | |
| step7, SUBSTITUTE(step6, "6", UNICHAR(8326)), | |
| step8, SUBSTITUTE(step7, "7", UNICHAR(8327)), | |
| step9, SUBSTITUTE(step8, "8", UNICHAR(8328)), | |
| step10, SUBSTITUTE(step9, "9", UNICHAR(8329)), | |
| step11, SUBSTITUTE(step10, "+", UNICHAR(8330)), | |
| step12, SUBSTITUTE(step11, "-", UNICHAR(8331)), | |
| step12 | |
| ) | |
| ); | |
| // Formats a number in scientific notation with specified significant figures | |
| SciNot = LAMBDA(SourceNumber, SigFigs, | |
| LET( | |
| rawExp, ROUNDDOWN(LOG10(ABS(SourceNumber)), 0), | |
| Exponent, IF(rawExp < 0, rawExp - 1, rawExp), | |
| IF( | |
| OR(Exponent > 1, Exponent < 0), | |
| LET( | |
| Coefficient, ROUND(SourceNumber * 10 ^ (-1 * Exponent), SigFigs - 1), | |
| ExpText, TEXT(Exponent, "0"), | |
| SuperExp, LET( | |
| step1, SUBSTITUTE(ExpText, "0", UNICHAR(8304)), | |
| step2, SUBSTITUTE(step1, "1", "¹"), | |
| step3, SUBSTITUTE(step2, "2", "²"), | |
| step4, SUBSTITUTE(step3, "3", "³"), | |
| step5, SUBSTITUTE(step4, "4", UNICHAR(8308)), | |
| step6, SUBSTITUTE(step5, "5", UNICHAR(8309)), | |
| step7, SUBSTITUTE(step6, "6", UNICHAR(8310)), | |
| step8, SUBSTITUTE(step7, "7", UNICHAR(8311)), | |
| step9, SUBSTITUTE(step8, "8", UNICHAR(8312)), | |
| step10, SUBSTITUTE(step9, "9", UNICHAR(8313)), | |
| step11, SUBSTITUTE(step10, "+", UNICHAR(8314)), | |
| step12, SUBSTITUTE(step11, "-", UNICHAR(8315)), | |
| step12 | |
| ), | |
| Coefficient & " x 10" & SuperExp | |
| ), | |
| ROUND(SourceNumber, SigFigs - 1) | |
| ) | |
| ) | |
| ); | |
| // Generates array of unique random integers in a range | |
| // Usage: Enter as array formula in desired range, or specify rows/cols | |
| RandInt = LAMBDA([nStart], [nEnd], [rows], [cols], | |
| LET( | |
| nStart, IF(ISOMITTED(nStart), 1, nStart), | |
| rows, IF(ISOMITTED(rows), 1, rows), | |
| cols, IF(ISOMITTED(cols), 1, cols), | |
| nCount, rows * cols, | |
| nEnd, IF(ISOMITTED(nEnd), nStart + nCount - 1, nEnd), | |
| range, nEnd - nStart + 1, | |
| IF( | |
| nCount = 1, | |
| RANDBETWEEN(nStart, nEnd), | |
| IF( | |
| nCount > range, | |
| "#NUM! Error: Range too small", | |
| LET( | |
| randomSeq, RANDARRAY(range, 1), | |
| sequence, SEQUENCE(range, 1, nStart, 1), | |
| sorted, SORTBY(sequence, randomSeq), | |
| result, TAKE(sorted, nCount), | |
| IF(rows = 1, TOROW(result, 1, TRUE), WRAPROWS(result, cols)) | |
| ) | |
| ) | |
| ) | |
| ) | |
| ); | |
| // Rounds a number to specified significant figures | |
| SigFigs = LAMBDA(SFInput, howmany, | |
| LET( | |
| rawLog, LOG10(ABS(SFInput)), | |
| Exponent, IF(rawLog < 0, INT(rawLog) - 1, INT(rawLog)), | |
| inscinot, SFInput / (10 ^ Exponent), | |
| roundedback, ROUND(inscinot, howmany - 1) * 10 ^ Exponent, | |
| roundedback | |
| ) | |
| ); | |
| //Number to Text Conversion | |
| //e.g. =SpellNumber(1234.56) → "One Thousand Two Hundred Thirty-Four and Fifty-Six Hundredths" | |
| SpellNumber = LAMBDA(myNumber, | |
| LET( | |
| numberString, TRIM(TEXT(myNumber, "0.#########")), | |
| decimalPlace, FIND(".", numberString), | |
| originalNumber, numberString, | |
| placeArray, {"", "", " Thousand ", " Million ", " Billion ", " Trillion "}, | |
| decPlaceArray, {"", " Tenths", " Hundredths", " Thousandths", " Ten-Thousandths", | |
| " Hundred-Thousandths", " Millionths", " Ten-Millionths", | |
| " Hundred-Millionths", " Thousand-Millionths", " Billionths"}, | |
| GetDigit, LAMBDA(digit, | |
| LET( | |
| val, VALUE(digit), | |
| SWITCH(val, | |
| 1, "One", | |
| 2, "Two", | |
| 3, "Three", | |
| 4, "Four", | |
| 5, "Five", | |
| 6, "Six", | |
| 7, "Seven", | |
| 8, "Eight", | |
| 9, "Nine", | |
| "" | |
| ) | |
| ) | |
| ), | |
| GetTens, LAMBDA(tensText, | |
| LET( | |
| firstDigit, VALUE(LEFT(tensText, 1)), | |
| secondDigit, RIGHT(tensText, 1), | |
| IF(firstDigit = 1, | |
| SWITCH(VALUE(tensText), | |
| 10, "Ten", | |
| 11, "Eleven", | |
| 12, "Twelve", | |
| 13, "Thirteen", | |
| 14, "Fourteen", | |
| 15, "Fifteen", | |
| 16, "Sixteen", | |
| 17, "Seventeen", | |
| 18, "Eighteen", | |
| 19, "Nineteen", | |
| "" | |
| ), | |
| LET( | |
| tensWord, SWITCH(firstDigit, | |
| 2, "Twenty", | |
| 3, "Thirty", | |
| 4, "Forty", | |
| 5, "Fifty", | |
| 6, "Sixty", | |
| 7, "Seventy", | |
| 8, "Eighty", | |
| 9, "Ninety", | |
| "" | |
| ), | |
| onesWord, GetDigit(secondDigit), | |
| IF(AND(tensWord <> "", onesWord <> ""), | |
| tensWord & "-" & onesWord, | |
| tensWord & onesWord | |
| ) | |
| ) | |
| ) | |
| ) | |
| ), | |
| GetHundreds, LAMBDA(myNumber, | |
| LET( | |
| paddedNumber, RIGHT("000" & myNumber, 3), | |
| hundredsDigit, MID(paddedNumber, 1, 1), | |
| tensPart, MID(paddedNumber, 2, 2), | |
| onesDigit, MID(paddedNumber, 3, 1), | |
| hundredsWord, IF(hundredsDigit <> "0", GetDigit(hundredsDigit) & " Hundred ", ""), | |
| tensWord, IF(MID(paddedNumber, 2, 1) <> "0", | |
| GetTens(tensPart), | |
| GetDigit(onesDigit) | |
| ), | |
| hundredsWord & tensWord | |
| ) | |
| ), | |
| ProcessNumberGroup, LAMBDA(ProcessNumberGroup, numberString, placeArray, count, | |
| LET( | |
| currentGroup, RIGHT(numberString, 3), | |
| remainingString, IF(LEN(numberString) > 3, | |
| LEFT(numberString, LEN(numberString) - 3), | |
| "" | |
| ), | |
| groupText, GetHundreds(currentGroup), | |
| placeText, IF(AND(groupText <> "", count <= ROWS(placeArray)), | |
| INDEX(placeArray, count), | |
| "" | |
| ), | |
| result, groupText & placeText, | |
| IF(remainingString = "", | |
| result, | |
| ProcessNumberGroup(ProcessNumberGroup, remainingString, placeArray, count + 1) & result | |
| ) | |
| ) | |
| ), | |
| ProcessDecimalPart, LAMBDA(decimalString, | |
| LET( | |
| decimalLength, LEN(decimalString), | |
| IF(decimalLength = 0, | |
| "", | |
| GetHundreds(decimalString) | |
| ) | |
| ) | |
| ), | |
| IF(decimalPlace > 0, | |
| LET( | |
| decimalPart, MID(originalNumber, decimalPlace + 1, LEN(originalNumber) - decimalPlace), | |
| integerPart, LEFT(numberString, decimalPlace - 1), | |
| cents, ProcessDecimalPart(decimalPart), | |
| dollars, IF(integerPart = "", | |
| "Zero", | |
| ProcessNumberGroup(ProcessNumberGroup, integerPart, placeArray, 1) | |
| ), | |
| centsText, IF(cents = "", "", " and " & cents & INDEX(decPlaceArray, LEN(decimalPart))), | |
| dollars & centsText | |
| ), | |
| LET( | |
| dollars, IF(numberString = "0", | |
| "Zero", | |
| ProcessNumberGroup(ProcessNumberGroup, numberString, placeArray, 1) | |
| ), | |
| dollars | |
| ) | |
| ) | |
| ) | |
| ); | |
| //Converts a decimal into a fraction. | |
| //You can specify the denominator and it will find the closest fit (reducing fraction if possible) | |
| //You can specify improper (default) or proper | |
| as_fraction = LAMBDA(number_, [denom], [improper], | |
| LET( | |
| denominator, IF(ISOMITTED(denom), -1, denom), | |
| improper_fraction, IF(ISOMITTED(improper), TRUE, improper), | |
| number, number_, | |
| IF(denominator <> -1, | |
| // Specified denominator - use Excel's TEXT formatting then reduce if possible | |
| LET( | |
| format_string, IF(improper_fraction, "?????/" & denominator, "# ?????/" & denominator), | |
| excel_result, TEXT(number, format_string), | |
| // Check if it's a fraction and reduce if possible | |
| IF(IFERROR(FIND("/", excel_result) > 0, FALSE), | |
| LET( | |
| // Extract numerator and denominator | |
| slash_pos, FIND("/", excel_result), | |
| numerator_str, IF(improper_fraction, | |
| MID(excel_result, 1, slash_pos - 1), | |
| LET( | |
| space_pos, IFERROR(FIND(" ", excel_result), 0), | |
| IF(space_pos > 0, MID(excel_result, space_pos + 1, slash_pos - space_pos - 1), MID(excel_result, 1, slash_pos - 1)) | |
| ) | |
| ), | |
| denominator_str, MID(excel_result, slash_pos + 1, LEN(excel_result) - slash_pos), | |
| numerator, VALUE(numerator_str), | |
| denom_value, VALUE(denominator_str), | |
| // Reduce using GCD | |
| gcd, GCD(ABS(numerator), ABS(denom_value)), | |
| reduced_num, numerator / gcd, | |
| reduced_den, denom_value / gcd, | |
| // Format result | |
| IF(improper_fraction, | |
| TEXT(reduced_num, "0") & "/" & TEXT(reduced_den, "0"), | |
| LET( | |
| whole_part, INT(number), | |
| IF(whole_part <> 0, | |
| TEXT(whole_part, "0") & " " & TEXT(reduced_num, "0") & "/" & TEXT(reduced_den, "0"), | |
| TEXT(reduced_num, "0") & "/" & TEXT(reduced_den, "0") | |
| ) | |
| ) | |
| ) | |
| ), | |
| excel_result | |
| ) | |
| ), | |
| // Auto-detect best fraction - use Excel's TEXT formatting (already reduced) | |
| LET( | |
| format_string, IF(improper_fraction, "??????????/??????????", "# ??????????/??????????"), | |
| TEXT(number, format_string) | |
| ) | |
| ) | |
| ) | |
| ); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment