Nested, and multiple IF statements

bob7 wrote:

tiered interest percentage where a the first $500,000.99 gets a 1.2% rate, and any remainder above gets a 1% rate.

Tiered rate problems are hard! I've had good look with the following approach, which avoids the confusion of IF or IFS, etc., and reduces to a mechanical exercise of filling in your brackets (tiers).

=MAX(0,(MIN(500000.99,A2)−0))*0.012/12
+MAX(0,(MIN(10000000,A2)−500000.99))*0.01/12

If your amount is in C7 then wherever you see an A2 in the formula change that to C7.

=MAX(0,(MIN(500000.99,C7)−0))*0.012/12
+MAX(0,(MIN(10000000,C7)−500000.99))*0.01/12

A little trick: if you are having trouble with a long formula in the formula editor you can separate it into lines using option-return, as I did for the screenshot.

SG

Hi Bob,

I use this mnemonic for IF formulas: IF it is raining, THEN stay at home, ELSE have a picnic.

You can simplify the formula (and remove several parentheses) by using another table with 50000.99 and calculations for 0.012/12 and 0.01/12 and referring to those cells.

Also, if C7 is not less than or equal to 500000.99, then it must be greater than 500000.99. No need for the second IF.

Also, the semicolon (;) should be a comma (,) 😉.

Also, I used option return in the formula editor to insert new lines (and automatic indents) to make the formula more easy for humans to read.

After all those riders, here we go.

Formula in Table 1 D7 is

IF(C7≤Constants::$B$2,
 Constants::$B$3×C7, Constants::$B$3×Constants::$B$2(Constants::$B$4×(C7−Constants::$B$2))
)

Fill down to test other values of data.

Are those the results that you expect?

Regards,

Ian.

Bob,

Table Functions are VERY picky about punctuation, as you now know. Glad you got it working.

SG gave you a good expression for clipping at 0. MAX(0, XXX) where XXX might go negative otherwise.

Jerry

Bob,

This is the IF syntax for the IF statement: IF(if-expression, if-true, if-false).

To nest another IF statement, replace if-true or if-false with another entire IF statement.

Jerry

Bob.

There's not much new in this post, but I just thought I'd show you my preference of keeping the constants as simple as possible for as long as possible.

Jerry

Hi Bob,

Glad to help. BTW, did you need semicolons to get the formula to work? Just wondering.

Regards,

Ian.