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: ``$ is an absolute reference if I recall correctly, I fail to see how this is changed from what I was doing other than limiting scalability? I have around 6 columns, and limiting it to absolute row doesn't change what I was doing?``
I wrote a partial or mixed absolute reference; namely B$3:B$43, not $B$3:$B$43.
Since you wrote ``so I don't have to type "SUM(#3:#43)" over and over``, I presumed that you have multiple colums of data in rows 3:43.
By using B$3:B$43 in the initial formula in column B, you can copy the formula to column C, D, E etc, and the formula will automatically change the data reference to C$3:C$43, D$3:D$43, E$3:E$43 etc.
Is that what you intended?
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()