Forum Discussion

jhermesP's avatar
jhermesP
Copper Contributor
May 30, 2019
Solved

Issue with my IF statement

I'm trying to do an if statement where based on the number of employees it will spit out a price, but the way my formula is set up if there's 0 employees it will spit out a $1. I tried to handle this...
excel
  • Twifoo's avatar
    Twifoo
    Jun 03, 2019
    Given your latest clarification, the formula in M11 is perhaps:
    =LOOKUP(C11,
    {0,1,101,251,501,1001},
    {0,1,1000,2000,3000,5000})
Twifoo's avatar
Twifoo
Silver Contributor
May 30, 2019
Your innermost IF returns 5000 whether or not C11>3000. Please clarify your logic by giving me 6 answers to this question.
What do you want the formula to return if C11 is:
1. 0
2. >1
3. >250
4. >500
5. >1000
6. >3000
  • jhermesP's avatar
    jhermesP
    Copper Contributor
    May 30, 2019

    Twifoo 

    Default greater than 3000 to $5000

    3000< = $5000

    1000< = $3000

    500 < = $2000

    250 < = $1000

    100 < =$1

    0 = 0

    • Twifoo's avatar
      Twifoo
      Silver Contributor
      May 30, 2019
      Perhaps, this formula will return your desired result:
      =LOOKUP(C11,
      {0,100,250,500,1000,3000},
      {0,1,1000,2000,3000,5000})
      • jhermesP's avatar
        jhermesP
        Copper Contributor
        May 30, 2019

        Twifoo 

        Just realized all of those carrots were facing the wrong way, but the concept should still be there. I'm trying to do number ranges so between 1000-3000 should print $5000 and so on.