SOLVED

help me with creating a formula

Copper Contributor

I NEED A FORMULA LIKE THIS

IF CELL K2 VALUE IS <5 RESULT SHOULD DISPLAY "NO DUES"

IF CELL K2 VALUE IS >5 AND <40 RESULT SHOULD DISPLAY "S DUES"

IF CELL K2 VALUE IS >40 AND <75 RESULT SHOULD DISPLAY "M DUES"

IF CELL K2 VALUE IS >75 AND <100 RESULT SHOULD DISPLAY "L DUES"

IF CELL K2 VALUE IS >100 AND <500 RESULT SHOULD DISPLAY "OVER DUE"

 PLEASE HELP ME IN THIS

EMAIL ID : shrirameshgovind@gmail.com

12 Replies
USING MS EXCEL 2007
OS WINDOWS 7 ULTIMATE

@RAMESHGOVIND I would suggest a table with the limits and VLOOKUP to get the result;

=VLOOKUP(K2;O2:Q7;3)

bosinander_0-1633071972183.png

 

The table can, if you like, be moved to another sheet with preferences (cut and paste).

LET ME TRY. THANKS FOR YOUR TIME

@RAMESHGOVIND VLOOKUP and a lookup table is imho clear and easy to both maintain and pass on to another user that may iherit the model. 

If you prefer all in one cell, you can use

=CHOOSE(MATCH(K2;{0;5;40;75;100;500});"No DUES";"S DUES";"M DUES";"L DUES";"OVER DUE";"#N/A")

It's been a while since I used 2007 but {0;5;40;75;100;500} is an array of numbers like P2:P6. Plus a leading zero.

bosinander_0-1633074549109.png

An even more powerful alternative than choose is switch, but in this case choose is enough.

 

In later Excel versions, managing dynamic arrays, you can enter ={0;5;40;75;100;500} and see how they propagate from the anchor cell and downwards as a list of numbers.

bosinander_1-1633074666237.png

 

IT IS NOT FOR ONE CELL FROM K2 TO K 1293

@RAMESHGOVIND 

As variant

=LOOKUP(K2, {0,5,40,75,100},{"NO DUES","S DUES","M DUES","L DUES","OVER DUE"})

and drag it down

THANKS SIR, BUT I NEED THIS RESULT IN ENTIRE WORK BOOK. PLS LOOK FORWARD THE IMG WHICH I UPLOADED. THANKS FOR THE VALUABLE TIME
THANKS IT WORKED
best response confirmed by RAMESHGOVIND (Copper Contributor)
Solution

@RAMESHGOVIND 

Great, thank you for the update

1 best response

Accepted Solutions
best response confirmed by RAMESHGOVIND (Copper Contributor)
Solution

@RAMESHGOVIND 

Great, thank you for the update

View solution in original post