Forum Discussion
creating a fee calculator for tiered percentages of rent savings
I certainly hope you see this, that you figure out we had miscommunicated--more precisely that I had misinterpreted your original request.
You don't say what version of Excel you're working with, but here's a much more fancy solution. It happens to require a current version of Excel, ideally a subscription to Microsoft 365.
I've attached a file that uses this solution. I also wrote a specific recommendation in a text box on that spreadsheet -- basically that you copy over the named ranges and this formula in their entirety.
If you need help, post a copy of your actual workbook and I'll implement this set of tables and the formula in your sheet.
In the meantime, here's the formula, with some explanation
=LET( enables us to name variables
feerow,MATCH(A2,Up_to,1), sets feerow as the row to use below
feebase,INDEX(BaseFee,feerow), sets feebase as the basic savings from BaseFee table
Pct2Add,INDEX(SavPct,feerow), sets Pct2Add as the Percent from SavPct table
overage,INDEX(over,feerow,1), sets100000 as base for calculating amnt over 100,000
MIN(MaxFee, starts MIN formula, to yield lesser of 10,000 or
feebase+((A2-overage)*Pct2Add) the result of feebase + (Pct of A2 - overage)
) end of MIN formula
) end of LET function
- This is terrific! I cant thank you enough. Trying to figure out a formula stumped our whole team!
You can answer your own question by testing the two of them side-by-side.
However, even if it did work, it's not a good formula for the reasons I gave you already. First of all, you don't need to even mention the 233333.33, using the MIN(10000,.... takes care of that AND allows you to go higher with that max if ever so desired.
I'm attaching another spreadsheet here where I give you both that formula I last posted, AND an example of how you could write one using named ranges, and I've got the named ranges in this spreadsheet. If you read up on them (using the link in my last post), you should have no trouble implementing that. But at the very least, I'd say just use the one I gave you. It's simpler than what you're trying to do by incorporating that 233333.33 value; it's really superfluous when you enter the MIN(10000...
Finally, if you are able to remove real names and other confidential info from your actual, you could post it (use some fake names to keep it "real") on OneDrive or GoogleDrive, with a link here that grants access. That way I could modify whatever you have there to make sure it works. Optional of course, but if it would be helpful to you.
- I was able to create this formula based on the help you gave me. Will it return the same results as the one you just sent me?
=IF(AJ2>233333.33,10000,IF(AJ2<100000,AJ13*0.06,((AJ2-100000)*0.03)+6000)) Well, how about that?! Turns out, after all my complicated and super-sophisticated formulas, that a very simple formula will work. I just had to reverse my thinking, take care of the larger numbers first.
=MIN(10000,IF(AJ12>100000,6000+((AJ12-100000)*0.03),AJ12*0.06))
All this does is say,
IF the value in AJ12 is greater than 100,000, then take 6,000 and add 3% times whatever amount is in excess of 100000;
IF NOT, calculate 6% of whatever AJ12 does contain.
All of that contained in a MIN function that limits the amount to whatever is the lesser, that calculation OR 10,000
By the way, there is no need to specify that upper limit of 233,333 as it really doesn't matter.
However, and this is an important lesson in Excel usage over the longer term, if you see yourself continuing to use Excel for other purposes: in general it's smart to NOT put numbers--any of those numbers in this case, the percentages, the 10000 max, the 100000 cutoff-- into a formula. It's called the "hard-coding of variables." Why is not smart not to put them directly into a formula: Because over time things change. You might want to have a different max; you might make it 7% and 2%; you might want the 6% to go up to 150,000 rather than 100,000. And so forth. When the numbers are hard-coded into the formula, this requires changing the formula. But if you can use a named range (or multiple named ranges), then all you need to do is change the variable in that visible cell.