SOLVED
Home

Issue with my IF statement

%3CLINGO-SUB%20id%3D%22lingo-sub-658639%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20my%20IF%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-658639%22%20slang%3D%22en-US%22%3EYour%20innermost%20IF%20returns%205000%20whether%20or%20not%20C11%26gt%3B3000.%20Please%20clarify%20your%20logic%20by%20giving%20me%206%20answers%20to%20this%20question.%3CBR%20%2F%3EWhat%20do%20you%20want%20the%20formula%20to%20return%20if%20C11%20is%3A%3CBR%20%2F%3E1.%200%3CBR%20%2F%3E2.%20%26gt%3B1%3CBR%20%2F%3E3.%20%26gt%3B250%3CBR%20%2F%3E4.%20%26gt%3B500%3CBR%20%2F%3E5.%20%26gt%3B1000%3CBR%20%2F%3E6.%20%26gt%3B3000%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-658711%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20my%20IF%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-658711%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDefault%20greater%20than%203000%20to%20%245000%3C%2FP%3E%3CP%3E3000%26lt%3B%20%3D%20%245000%3C%2FP%3E%3CP%3E1000%26lt%3B%20%3D%20%243000%3C%2FP%3E%3CP%3E500%20%26lt%3B%20%3D%20%242000%3C%2FP%3E%3CP%3E250%20%26lt%3B%20%3D%20%241000%3C%2FP%3E%3CP%3E100%20%26lt%3B%20%3D%241%3C%2FP%3E%3CP%3E0%20%3D%200%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-658756%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20my%20IF%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-658756%22%20slang%3D%22en-US%22%3EPerhaps%2C%20this%20formula%20will%20return%20your%20desired%20result%3A%3CBR%20%2F%3E%3DLOOKUP(C11%2C%3CBR%20%2F%3E%7B0%2C100%2C250%2C500%2C1000%2C3000%7D%2C%3CBR%20%2F%3E%7B0%2C1%2C1000%2C2000%2C3000%2C5000%7D)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-658948%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20my%20IF%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-658948%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20realized%20all%20of%20those%20carrots%20were%20facing%20the%20wrong%20way%2C%20but%20the%20concept%20should%20still%20be%20there.%20I'm%20trying%20to%20do%20number%20ranges%20so%20between%201000-3000%20should%20print%20%245000%20and%20so%20on.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-659030%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20my%20IF%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-659030%22%20slang%3D%22en-US%22%3EPlease%20explain%20your%20requirement%20by%20following%20the%20pattern%20I%20earlier%20mentioned%20as%20items%201%20to%206.%20Your%20use%20of%20the%20phrase%20%E2%80%9Cand%20so%20on%E2%80%9D%20cannot%20be%20translated%20to%20a%20formula.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-664643%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20my%20IF%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-664643%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20C11%3D0%2C%20M11%3D0%3C%2FP%3E%3CP%3EIf%201%3CC11%3E%26lt%3B100%2C%20M11%3D%241%3C%2FC11%3E%3C%2FP%3E%3CP%3EIf%20101%3CC11%3E%26lt%3B250%2C%20M11%3D%241000%3C%2FC11%3E%3C%2FP%3E%3CP%3EIf%20251%3CC11%3E%26lt%3B500%2C%20M11%3D%242000%3C%2FC11%3E%3C%2FP%3E%3CP%3EIf%20501%3CC11%3E%26lt%3B1000%2C%20M11%3D%243000%3C%2FC11%3E%3C%2FP%3E%3CP%3EIf%20C11%26gt%3B1000%2C%20M11%3D%245000%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-665089%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20my%20IF%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-665089%22%20slang%3D%22en-US%22%3EGiven%20your%20latest%20clarification%2C%20the%20formula%20in%20M11%20is%20perhaps%3A%3CBR%20%2F%3E%3DLOOKUP(C11%2C%3CBR%20%2F%3E%7B0%2C1%2C101%2C251%2C501%2C1001%7D%2C%3CBR%20%2F%3E%7B0%2C1%2C1000%2C2000%2C3000%2C5000%7D)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-665093%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20my%20IF%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-665093%22%20slang%3D%22en-US%22%3EPerfect!%20Thank%20you!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-665095%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20my%20IF%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-665095%22%20slang%3D%22en-US%22%3EI%E2%80%99m%20delighted%20to%20have%20finally%20given%20you%20a%20solution!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-658080%22%20slang%3D%22en-US%22%3EIssue%20with%20my%20IF%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-658080%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20do%20an%20if%20statement%20where%20based%20on%20the%20number%20of%20employees%20it%20will%20spit%20out%20a%20price%2C%20but%20the%20way%20my%20formula%20is%20set%20up%20if%20there's%200%20employees%20it%20will%20spit%20out%20a%20%241.%20I%20tried%20to%20handle%20this%20by%20reversing%20the%20IF%20statement.%20It%20didn't%20work%20so%20I%20tried%20to%20handle%20it%20with%20an%20IF%20statement%20later%20to%20handle%20that%20in%20the%20total%20sum%2C%20but%20that%20lead%20to%20it%20not%20printing%20the%20sum%20altogether.%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20the%20M11%20formula%26nbsp%3B%3DIF(C11%26gt%3B1%2C%20IF(C11%26gt%3B250%2CIF(C11%26gt%3B500%2C%20IF(C11%26gt%3B1000%2CIF(C11%26gt%3B3000%2C5000%2C5000)%2C3000)%2C2000)%2C1000)%2C%201)%3C%2FP%3E%3CP%3Eand%20here's%20the%20M13%20forumla%26nbsp%3B%3DIF(1%2C0%2C(M10%2BM11%2BM12))%3C%2FP%3E%3CP%3EWould%20prefer%20to%20fix%20the%20M11%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-658080%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
jhermesP
Occasional 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

 

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!
Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies