Forum Discussion

RAMESHGOVIND's avatar
RAMESHGOVIND
Copper Contributor
Oct 01, 2021
Solved

help me with creating a formula

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 DISPLA...
Excel for web
Formulas and Functions
RAMESHGOVIND's avatar
RAMESHGOVIND
Copper Contributor
Oct 01, 2021
USING MS EXCEL 2007
OS WINDOWS 7 ULTIMATE
  • bosinander's avatar
    bosinander
    Iron Contributor
    Oct 01, 2021

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

    =VLOOKUP(K2;O2:Q7;3)

     

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

    • RAMESHGOVIND's avatar
      RAMESHGOVIND
      Copper Contributor
      Oct 01, 2021
      LET ME TRY. THANKS FOR YOUR TIME
      • bosinander's avatar
        bosinander
        Iron Contributor
        Oct 01, 2021

        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.

        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.

         

Resources