Using LET function with arguments?

[mrblister]

I have a very long formula, that I currently have to repeat with different arguments.

For example, let's say the long formula is: "addDecimalOne(var)", which the formula adds 0.1 to "var".

I want to run that formula with 2 different vars, then sum it up.

Is there a way to use the LET formula (Excel 2021) to shorten that formula?

This is as close as I can get:
"=LET(var,1,addDecimalOne,(var+0.1+N("this simulates super long formula")),addDecimalOneA,addDecimalOne,var,2,addDecimalOneB,addDecimalOne,addDecimalOneA+addDecimalOneB)"

But Excel won't let me run that formula, as the following error appears due to the second "var": "You can't define the same name twice in a LET function."

Any advice? Note I'm using Excel 2021, so I have access to LET but not necessarily newer functions included in Excel 365.

 

[duggie33]

Hi mrblister,

Here is a simple example of using the LET function...

Book1
	A	B
1	a	3
2	b	7
3
4	Let formula	21
Sheet2

Cell Formulas
Range	Formula
B4	B4	=LET(a,$B$1,b,$B$2,IF(a>b,a+b,a*b))

You might be thinking of making a LAMBDA function. They are generally more complex to write, but much more powerful; like creating your own functions.

Hope that helps,

Doug

 

[mrblister]

Thanks for replying Doug. Unfortunately, Excel 2021 doesn't include LAMDA. Is there no way to do what I want with LET?

 

[duggie33]

It can probably be done, but I couldn't follow exactly what you are trying to do...I am getting tired early tonight LOL. Maybe you could provide an example of your inputs with expected output and post it with XL2BB if you have that option. I almost always have to build formulas step-by-step in multiple cells, and them combine them into the LET function.

Doug

 

[mrblister]

OK, this problem is coming back to haunt me again. I have a long formula fragment, that most of it is repeating parts but has an argument/variable-reference that changes.

For example, let's say the repeating part of the formula is "1+x", the arguments are 1, 2, 3, 4, 5 and the output is (1+(1)) + (1+(2)) + (1+(3)+ (1+(4)+ (1+(5)) = 20.

Using LET and LAMBDA this formula would work:
=LET(myformula,LAMBDA(input,1+input),myformula(1)+myformula(2)+myformula(3)+myformula(4)+myformula(5))

In my example about I used "1+x" as the repeating fragment with an argument input, BUT in reality my repeating fragment is actually very long for the formula I'm working on.

But I'm using Excel 2021, which doesn't have LAMBDA. Can I still shorten my formula using LET WITHOUT LAMBDA? Could someone modify my example above on how it could be done?

 

[RoryA]

No, you can't use LET to store a function. It might be possible to use arrays or other alternatives, but we'd have to know what the actual formula is.

 

[djclements]

Both of the examples you've described can be achieved simply by adding the constant (0.1 or 1 in your examples above) to an array of values, then summing the results. For example:

=SUM(1+{1,2,3,4,5})

The SEQUENCE function should be available in Excel 2021, so the following would produce the same results:

=SUM(1+SEQUENCE(5))

The same would also work if your variables are stored in a worksheet range:

=SUM(1+A1:A5)

Or, if the variables are in non-contiguous cells, you could use the CHOOSE function with SEQUENCE to stack the variables in an array:

=SUM(1+CHOOSE(SEQUENCE(5),A1,B5,C2,D4,E3))

I hope that helps. Cheers!