Last active
June 15, 2023 10:11
-
-
Save jsb2505/1e616b709303549e2674ce52555bae01 to your computer and use it in GitHub Desktop.
Excel Lambda function to find alpha value
This file contains 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
/* | |
A function to calculate alpha as given by Appendix G in SCI P398 | |
INPUTS: m, m2 and e | |
RETURNS: value of alpha | |
Example: | |
=GetAlpha(40,45,35) | |
Returns: | |
5.345 | |
Note: | |
The last optional parameter is used for recurrsion and should not be inputted by the user. | |
The function does not check the validity of the input. | |
The result is accurate to the nearest 0.005. | |
*/ | |
GetAlpha = LAMBDA(m, m_2, e, [alpha_DONT_INPUT], | |
LET( | |
lambda_1, m / (m + e), | |
lambda_2, m_2 / (m + e), | |
alpha, IF(ISOMITTED(alpha_DONT_INPUT), 4.45, alpha_DONT_INPUT), | |
lambda_1_temp, GetLambda1(alpha, lambda_2), | |
IF( | |
(lambda_1 / lambda_1_temp) < 1, | |
GetAlpha(m, m_2, e, alpha + 0.005), | |
MIN(alpha, 8.00) | |
) | |
) | |
); | |
GetLambda1 = LAMBDA(alpha, lambda_2, | |
LET( | |
lambda_1_lim, 1.25 / (alpha - 2.75), | |
lambda_2_lim, alpha * lambda_1_lim / 2, | |
IF( | |
lambda_2 >= lambda_2_lim, | |
lambda_1_lim, | |
lambda_1_lim + (1 - lambda_1_lim) * ((lambda_2_lim - lambda_2) / lambda_2_lim) ^ (0.185 * alpha ^ 1.785) | |
) | |
) | |
); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment