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) )
I don't know why you feel the need to replace the explicit range reference with a named reference. IMHO, that might obscure details and introduce error.
Perhaps you just need to write B$3:B$43 instead of B3:B43.
I have doubts about the correctness of your original formula. Read all of the "notes" below. But ostensibly, it can be replaced with:
=SUM(B$3:B$43)*(1 + DATEDIF($A$1,$A$2,"m")*10%) + MAX(SUM(B$3:B$43)*1.75%, 0.25)
where A1 has the date 10/16/2023, and A2 might have the formula =TODAY(). That makes it easy to try different date combinations in order to test correctness.
Note that we should write MAX, not MIN.
Notes:
1. Your original expression has a typo: an extra right parenthesis. If this means that the expression is part of a larger expression, it might behoove you to post the enter original formula, especially considering my note #xx below.
2. The loan interest rate of 10% per month seems exorbitant. That would be 120% per year, which would be usurious and illegal in most countries. I suspect that 10% is an annual rate, and we should use 10%/12 in the formula. Thus:
=SUM(B$3:B$43)*(1 + DATEDIF($A$1,$A$2,"m")*10%/12) + MAX(SUM(B$3:B$43)*1.75%, 0.25)
3. Usually, the PayPal fee (1.75%) applies to all funds paid, including the loan interest, not just SUM(B$3:B$43). To avoid unduly complicating the formula, we might calculate the base amount in another cell. For example:
A3: =SUM(B$3:B$43) * (1 + DATEDIF($A$1,$A$2,"m")*10%/12)
Then your formula becomes:
=A3 + MAX(A3*1.75%, 0.25)
4. In the real world, calculations must be rounded at least to the cent (1/100) for most currencies. Thus, your formulas become:
A3: =ROUND(SUM(B$3:B$43) * (1 + DATEDIF($A$1,$A$2,"m")*10%/12), 2)
=A3 + MAX(ROUND(A3*1.75%, 2), 0.25)
5. But if you use Excel 365 or Excel 2019 or later, you might use the LET function instead.
=LET( amt, ROUND(SUM(B$3:B$43) * (1 + DATEDIF($A$1,$A$2,"m")*10%/12), 2),
amt + MAX(ROUND(amt*1.75%, 2), 0.25) )
6. Finally, the original formula returns 0.25 if SUM(B$3:B$43) is zero (e.g. an empty range). If that does not bother you, fine. Otherwise, you might change the formula as follows:
=LET( amt, ROUND(SUM(B$3:B$43) * (1 + DATEDIF($A$1,$A$2,"m")*10%/12), 2),
amt + MAX(ROUND(amt*1.75%, 2), 0.25) * (SUM(B$3:B$43) > 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) )