IF function formula exceeding criteria limit

Question

=IF(P5=0.00,"0.05",IF(P5=10,000.00,"0.10",IF(P5=30,000.00,"0.15",IF(P5=70,000.00,"0.20",IF(P5=140,000.00,"0.25",IF(P5=250,000.00,"0.30",IF(P5=500,000.00,"0.32"))))
Excel wont accept it because it has too many arguments. Please help.

Answer 1

Excel won't accept your formula because you included commas in your numbers such as P5=10,000.00. With an IF function (or any function in Excel), commas are used to separate arguments, so Excel sees P5=10 as one argument, then 000.00 as the next argument and so on. To correct the problem, remove the commas from the numbers.

 

=IF(P5=0,"0.05",IF(P5=10000,"0.10",IF(P5=30000,"0.15",IF(P5=70000,"0.20",IF(P5=140000,"0.25",IF(P5=250000,"0.30",IF(P5=500000,"0.32")))))))

 

 

Answer 2

=IF(P5=0.00,"0.05",IF(P5=10,000.00,"0.10",IF(P5=30,000.00,"0.15",IF(P5=70,000.00,"0.20",IF(P5=140,000.00,"0.25",IF(P5=250,000.00,"0.30",IF(P5=500,000.00,"0.32")  )))

Microsoft Excel’s IF function has the general form:

      IF ( logical_test, [value_if_true], [value_if_false] )

  And it is worth your time to understand the meta-language (you know, like “noun” and “predicate” are used to describe an allowable sentence structure).

    logical_test  is like what you wrote as P5=0.00     (note:  this is not assignment, this tests whether P5 is equal to 0.00

    [value_if_true]  is the computed value the cell will contain if the logical_test is true

    [value_if_false] is the value the cell will contain if false

I noticed that you have nested other tests in order to compute [value_if_false].  I took the liberty (it helps me) of substituting my own meta-language variables (like you do with substitution in algebra) to reduce the nested IF tests (just for understanding):

=IF(P5=0.00,"0.05",IF(P5=10,000.00,"0.10",IF(P5=30,000.00,"0.15",IF(P5=70,000.00,"0.20",IF(P5=140,000.00,"0.25",IF(P5=250,000.00,"0.30", MLA )))

=IF(P5=0.00,"0.05",IF(P5=10,000.00,"0.10",IF(P5=30,000.00,"0.15",IF(P5=70,000.00,"0.20",IF(P5=140,000.00,"0.25", MLB))

=IF(P5=0.00,"0.05",IF(P5=10,000.00,"0.10",IF(P5=30,000.00,"0.15",IF(P5=70,000.00,"0.20", MLC )

  But I had to stop there because this was no longer an legal expression.  This is how the compiler part of Excel decides what you want the formula to do and it must issue an error because it can’t do anything!  (note: the error may not be very understandable to a human).  So, be sure evey IF test has 3 arguments.

Then, I looked at the numbers that you wrote and realized that P5=10,000.00 would not be accepted by Excel because of the “,”.  (Remember “,” separates arguments in the IF function – it is not used when a big number is to be expressed).

So, then I looked at

        =IF(P5=250,000.00,"0.30",IF(P5=500,000.00,"0.32") 

   And, with all commas separating arguments (not part of numbers), I counted three arguments in the inner nested If (that’s o.k.), but FOUR arguments in the outer function.   Thus, Excel might complain (as I am), “too many arguments.”

I’m not a computer, but this is pretty much how Excel interprets the stuff that you type in.

Hope you Excel at computer calculations.

Answer 3

You  may want to consider using vlookup instead.

The resulting formula is might be easier to understand and is much shorter.

It just requires a little setup.

 

Option 1: =VLOOKUP(P5,K3:L8,2,FALSE)

This will return exactly what your current formulas does.

Except when the exact value in not found, you will get #N/A instead of FALSE

 

Option 2: =VLOOKUP(P5,K3:L8,2)

This version will return the same results, but will also return results for values between those listed in your formula.

You can get this result with a minor change to your formula.

=IF(P5<10000,"0.05",IF(P5<30000,"0.10",IF(P5<70000,"0.15",IF(P5<140000,"0.20",IF(P5<250000,"0.25",IF(P5<500000,"0.30","0.32"))))))

 

The table used in the vlookup would look something like this.

K3:L8 in this example.

 

TestAmt Percent
         0    0.05
  10000    0.10
  30000    0.15
  70000    0.20
140000    0.25
250000    0.30
500000    0.32