Forum Discussion
Shortening a Long Excel Formula
I'm still relatively new to Excel and I'm trying to automate this but there's more than a few conditions and the length of the formula is getting a little out of hand.
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))
First off, this is so I can keep track of debts I owe to people. I am in a serious pinch and have taken money from friends that I intend to pay back via PayPal. that last part is important due to service fees for withdraw from PayPal.
So to break it down, there's a 1.75% service fee from PayPal that is added to the whole transaction, however it has a floor of $0.25. Next is an interest rate that accrues 10% each month, non compounding. Set to start on 11/16/2023. I feel like having variable declaration would help but the only way I can think to do that is to throw a value into another cell and use that as a "variable" so I don't have to type "SUM(#3:#43)" over and over. Also checking if that 1.75% of that sum is lower than 0.25 could probably be a shorter line but I have been blanking of how to phrase it for about an hour now and I'm honestly just fed up with thinking about it.
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) )
7 Replies
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?
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.
