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) )
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) )
Thanks a bunch! this is what I wound up using:
=LET(s, SUM(B3:B43), amt, ROUNDUP(s*(1+(DATEDIF($M$2, TODAY(), "M")*0.1)), 2), amt+MAX(ROUND(amt*1.75%, 2), 0.25)*(s>0))
where M2 is 10/16/2023.
I'm keeping TODAY() so that it's up to date all the time whenever I open the sheet. And as I mentioned above, it does in fact go up every month, not every year. Probably a insane decision on my part that will come to bite me later on and financially ruin me, but those are future problems for future me.
Thanks again! I didn't know I could assign local variables inside a single cell with LET()