 • 546K Members
• 2,421 Online
• 652K Conversations
SOLVED

## Issue with my IF statement

Highlighted
Occasional Contributor

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

9 Replies
Highlighted

# Re: Issue with my IF statement

What do you want the formula to return if C11 is:
1. 0
2. >1
3. >250
4. >500
5. >1000
6. >3000
Highlighted

# Re: Issue with my IF statement

Default greater than 3000 to \$5000

3000< = \$5000

1000< = \$3000

500 < = \$2000

250 < = \$1000

100 < =\$1

0 = 0

Highlighted

# Re: Issue with my IF statement

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

# Re: Issue with my IF statement

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.

Highlighted

# Re: Issue with my IF statement

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

# Re: Issue with my IF statement

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

Highlighted
Solution

# Re: Issue with my IF statement

Given your latest clarification, the formula in M11 is perhaps:
=LOOKUP(C11,
{0,1,101,251,501,1001},
{0,1,1000,2000,3000,5000})
Highlighted

# Re: Issue with my IF statement

Perfect! Thank you!
Highlighted

# Re: Issue with my IF statement

I’m delighted to have finally given you a solution!