Forum Discussion
creating a fee calculator for tiered percentages of rent savings
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.
=IF(AJ2>233333.33,10000,IF(AJ2<100000,AJ13*0.06,((AJ2-100000)*0.03)+6000))
- 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.