• 464K Members
• 9,007 Online
• 561K Conversations
SOLVED

## Issue with my IF statement

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

# 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

# Re: Issue with my IF statement

Default greater than 3000 to \$5000

3000< = \$5000

1000< = \$3000

500 < = \$2000

250 < = \$1000

100 < =\$1

0 = 0

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

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

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

# 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

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

# Re: Issue with my IF statement

Perfect! Thank you!

# Re: Issue with my IF statement

I’m delighted to have finally given you a solution!
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies