Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

function IF

0

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

thanks

ira

Answer
Discuss

Answers

0

here is a start you can do the rest

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

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.

=INDEX({"A","B","C","D"},1,MATCH($A1,{0,32.5,60,100},1))

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

Discuss


Answer the Question

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