Forum Discussion
Shortening a Long Excel Formula
SignalRaptor wrote: ``With relative references I just dragged the formula horizontally across and it updated automatically``
You're absolutely right. (Pun intended. wink) I don't know why I suggested the mixed absolute alternative. Again, I just reacted to your comment ``so I don't have to type "SUM(#3:#43)" over and over`` without giving it much thought.
Re: "if you have the same thing written over and over, you've done something inefficient". That was my primary interest, as well. And I think I addressed that issue.
PS.... If you wish, the LET expression could be:
=LET( s, SUM(B$3:B$43), amt, ROUND(s * (1 + DATEDIF($A$1,$A$2,"m")*10%/12), 2),
amt + MAX(ROUND(amt*1.75%, 2), 0.25) * (s > 0) )
To simplify and shorten your Excel formula, you can use named ranges and IF statements. Here's a more organized version of your formula with named ranges:
- Name the range B3:B43 as "LoanAmount" so you don't need to reference it multiple times.
- Create a named range named "ServiceFee" with the formula =MIN(LoanAmount * 0.0175, 0.25) to handle the service fee, ensuring it doesn't go below $0.25.
Now, you can use these named ranges in your formula. This makes your formula more readable and easier to maintain:
=SUM(LoanAmount) + ServiceFee + SUM(LoanAmount) * (DATEDIF("10/16/2023", TODAY(), "M") * 0.1)
With this approach, your formula is shorter and easier to understand. Additionally, by using named ranges, you can easily update the loan amount and the service fee in one place (the named range definitions) without changing the formula itself. This should make it more manageable for you.
To define named ranges, you can go to the Formulas tab in Excel, click on Define Name, and set the name and range reference for each named range. The text was created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.