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