Forum Discussion
Which Formula should I use?
I'd create a range with thresholds and the corresponding charges. In the screenshot below, it is G2:H5.
You can then use XLOOKUP (or VLOOKUP in older versions of Excel).
The formula in B2 is
=XLOOKUP(A2,$G$2:$G$5,$H$2:$H$5,"",-1)
shamilton35 If your fees follow a set pattern, you could use a formula to calculate the results. For example, if the base rate is $25.00 for the first 1000, plus $10.00 for every additional 1000, you could try something along these lines (with the size input in cell A2):
=INT(ROUNDUP(MAX(A2, 1)-1, 0)/1000)*10+25
- OR -
=FLOOR.MATH(ROUNDUP(MAX(A2, 1)-1, 0)/100, 10)+25
The IFS function can also be used, but there doesn't appear to be a need for the AND function in this case. The most common mistake people make with IF logic is to overcomplicate things when it's not necessary. For example, something like this might be all that you need:
=IFS(A2=0, 0, A2<=1000, 25, A2<=2000, 35, A2<=3000, 45, A2<=4000, 55, TRUE, 65)
The IFS function evaluates each logical test in order from left to right. When the first TRUE is found, it stops and returns the corresponding value, and the remaining tests are not even processed. To return a "default value" in the event that ALL tests evaluate to FALSE, set the final logical test to TRUE.
Hopefully that helps. Without seeing your complete fee schedule, the best I can offer are examples that demonstrate different options. Cheers!