function IF


formula fungsi IF

Dear all , 

i have data

0 - 32.5  =A

32.5 - 60 =B

60 - 100 =C

>100 = D

why use function IF






here is a start you can do the rest

=IF(A2<=32.5,"A",IF(A2<=60,"B","finish me"))

Why to use an IF function? Well, don't! Since you have 4 choices you will need 3 IF functions and nest them into each other. That results in a convoluted formula. Instead you can make your choice directly with INDEX/MATCH.


In plain English, the formula says to select the nth value among "A" to "D", where n itself is the nth value in a row of numbers which are bigger than the value in cell A1. Modify the cell address as required.

The array in MATCH($A1,{0,32.5,60,100},1) is constructed in ascending order and the MATCH function will find the largest number which is smaller or equal to than the value in A1. The point to pay attention to is equality. If A1 has a value of exactly 32.5 the function will return 2 (resulting in the selection of "B") because 32.5 is the lowest limit in the B-group. If you need 32.5 to be classified as "A" the value in the MATCH array should be 32.6 or 32.51


Answer the Question

You must create an account to use the forum. Create an Account or Login