SOLVED

Issue with my IF statement

Copper Contributor

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.

9 Replies
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

@Twifoo 

Default greater than 3000 to $5000

3000< = $5000

1000< = $3000

500 < = $2000

250 < = $1000

100 < =$1

0 = 0

Perhaps, this formula will return your desired result:
=LOOKUP(C11,
{0,100,250,500,1000,3000},
{0,1,1000,2000,3000,5000})

@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.

Please explain your requirement by following the pattern I earlier mentioned as items 1 to 6. Your use of the phrase “and so on” cannot be translated to a formula.

@Twifoo 

If C11=0, M11=0

If 1<C11<100, M11=$1

If 101<C11<250, M11=$1000

If 251<C11<500, M11=$2000

If 501<C11<1000, M11=$3000

If C11>1000, M11=$5000

 

best response confirmed by jhermesP (Copper Contributor)
Solution
Given your latest clarification, the formula in M11 is perhaps:
=LOOKUP(C11,
{0,1,101,251,501,1001},
{0,1,1000,2000,3000,5000})
Perfect! Thank you!
I’m delighted to have finally given you a solution!
1 best response

Accepted Solutions
best response confirmed by jhermesP (Copper Contributor)
Solution
Given your latest clarification, the formula in M11 is perhaps:
=LOOKUP(C11,
{0,1,101,251,501,1001},
{0,1,1000,2000,3000,5000})

View solution in original post