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) )
JoeUser2004
Reply to Notes:
1. That was left over from something stupid and unnecessary I was trying to do that you solved in note 6 anyway as "future proof" where I had an ISBLANK check to set the value to 0 for empty columns in the table I had placed in case I needed them, and then realized that idea was stupid due to the fact that I could just extend the table. The columns are people, and the contents of each column are amounts paid to me that I need to return. the full line is as follows:
=IF(ISBLANK(B3), 0, SUM(B3:B43)+IF((SUM(B3:B43)*0.0175>0.25), SUM(B3:B43)*0.0175, 0.25)+SUM(B3:B43)*(DATEDIF("10/16/2023",TODAY(),"M")*0.1))
2. You would be correct it is exorbitant, I don't intend to hold this debt very long and it's not a exactly a legal agreement, just verbal between people. 10% was agreed upon because I was hoping to incite more funds in the immediate because I need to eat, and was hoping to be able to pay it off before the end of this year (I believe I have secured stable employment for now). The total amount is still under $1k so I'm not worried about the 10%.
3. I just noticed that a while after posting after I left the library I was posting from and was trying to think of a way to include that.
4 & 5. I appreciate this as I completely forgot about this. Thank you. I'm using 365 for now so #5 is probably what I'll go with.
Notes of my own:
A. $ 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? If using absolute references does something other than I remember, please let me know. If I'm using cells as variable storage then absolute references would be necessary but as it stands I am not currently, and even if I was setting the rows to absolute doesn't change the data being processed.
B. I was trying to avoid using cells as variable storage simply because it adds clutter. I could do what I do if I'm working with exceptionally large and complex sets of data and make a variable sheet but I was trying to avoid that too because I didn't feel that was necessary for this, and would have been a waste of effort.
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?
- Sorry for the confusion, it was more about ease of readability and modification. With relative references I just dragged the formula horizontally across and it updated automatically, but I was trying to see if there was a way to clean it up so it was easier to modify as needed. Having to calculate the same sum several times in the same formula just hit the nerve that my programmer father always told me "if you have the same thing written over and over, you've done something inefficient"
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()