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 by reversing the IF statement. It didn't work so I tried to handle it with an IF statement later to handle that in the total sum, but that lead to it not printing the sum altogether.

Here's the M11 formula =IF(C11>1, IF(C11>250,IF(C11>500, IF(C11>1000,IF(C11>3000,5000,5000),3000),2000),1000), 1)

and here's the M13 forumla =IF(1,0,(M10+M11+M12))

Would prefer to fix the M11 formula.

excel

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})

9 Replies

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
  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
    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})

Forum Discussion

Issue with my IF statement