Multiple if

%3CLINGO-SUB%20id%3D%22lingo-sub-1445464%22%20slang%3D%22en-US%22%3EMultiple%20if%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1445464%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIs%20there%20a%20formula%20i%20can%20use%20for%20a%20multiple%20conditions%2C%20as%20i%20can%20not%20use%20%22IF%22%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThank%20you.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1445464%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1445473%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20if%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1445473%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F691056%22%20target%3D%22_blank%22%3E%40Asmaa950%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20can%20you%20not%20use%20%3CSTRONG%3EIF%3C%2FSTRONG%3E%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20you%20aware%20of%20%3CSTRONG%3EIFS%3C%2FSTRONG%3E%3F%20It%20covered%20multiple%20multiple%20conditions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETell%20us%20more%20what%20your%20situation%20is.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1445485%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20if%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1445485%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F691056%22%20target%3D%22_blank%22%3E%40Asmaa950%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20just%20occurred%20to%20me%20that%20you%20may%20not%20have%20been%20aware%20that%20you%20can%20nest%20one%20IF%20condition%20in%20another...that%20was%20how%20IF%20alone%20used%20to%20be%20for%20multiple%20conditions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20%3CSTRONG%3EIFS%3C%2FSTRONG%3E%20now%20is%20more%20efficient%20and%20more%20readable.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1445514%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20if%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1445514%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply%2C%3C%2FP%3E%3CP%3EYes%20you%20are%20right%20i%20do%20not%20have%20%22IFS%22%20formula%20in%20my%20excel%20version%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1445519%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20if%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1445519%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3Ethis%20formula%20does%20not%20work%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(V10%26lt%3B15000%2F12%2C0%2CIF(V10%26lt%3B30000%2F12%2C%22(V10-(15000%2F12))*2.5%25%22%2CIF(V10%26lt%3B45000%2F12%2C%22((V10-(30000%2F12))*10%25)%2B(375%2F12)%22%2CIF(V10%26lt%3B60000%2F12%2C((V10-(45000%2F12))*15%25)%2B(1875%2F12)%2CIF(V10%26lt%3B200000%2F12%2C((V10-(60000%2F12))*20%25)%2B(4125%2F12)%2CIF(V10%26lt%3B400000%2F12%2C((V10-(200000%2F12))*22.5%25)%2B(32125%2F12)%2CIF(V10%26lt%3B600000%2F12%2C%22((V10-(400000%2F12))*25%25)%2B(77125%2F12)%22%2CIF(V10%26lt%3B700000%2F12%2C%22((V10-(400000%2F12))*25%25)%2B(77500%2F12)%22%2CIF(V10%26lt%3B800000%2F12%2C((V10-(400000%2F12))*25%25)%2B(79750%2F12)%2CIF(V10%26lt%3B900000%2F12%2C((V10-(400000%2F12))*25%25)%2B(82000%2F12)%2CIF(V10%26lt%3B1000000%2F12%2C((V10-(400000%2F12))*25%25)%2B(85000%2F12)%2CIF(V10%26gt%3B1000000%2F12%2C((V10-(400000%2F12))*25%25)%2B(90000%2F12)%2C0))))))))))))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1445541%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20if%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1445541%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F691056%22%20target%3D%22_blank%22%3E%40Asmaa950%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20a%20formula%20like%20that%20doesn't%20work--and%20%22doesn't%20work%22%20doesn't%20tell%20us%20what%20does%20happen%2C%20what%20error%20message%20you're%20getting%2C%20or%20is%20it%20not%20an%20error%20message%20at%20all%2C%20just%20not%20the%20result%20that%20you%20expect%3F--I%20will%20usually%20take%20each%20individual%20condition%20and%20ensure%20first%20that%20it%20works%20separately.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20the%20most%20part%2C%20just%20for%20intelligibility%2C%20we're%20better%20served%20by%20not%20trying%20to%20build%20nested%20formulas%20quite%20so%20deeply%20nested.%20Most%20texts%20on%20Excel%20warn%20against%20them.%20It's%20fun%20to%20make%20them%20work%2C%20perhaps%2C%20but%20trying%20to%20go%20back%20and%20diagnose%20or%20fix%20when%20it%20%22doesn't%20work%22%20is%20always%20frustrating.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can't%20test%20the%20formula%20without%20the%20full%20sheet%2C%20and%20(frankly)%20I'm%20not%20going%20to%20devote%20the%20time%20to%20try%20to%20parse%20it.%20You%20clearly%20know%20the%20syntax%20of%20IF%3B%20it's%20not%20likely%20the%20case%20that%20IF%20%3CEM%3Eper%20se%3C%2FEM%3E%20isn't%20working%2C%20more%20that%20there's%20some%20relationship%20in%20there%20that%20causes%20failure...breaking%20it%20apart%20into%20separate%20IFs%20is%20what%20I'd%20recommend%20you%20do.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1445558%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20if%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1445558%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJust%20in%20case%2C%20that's%20duplication%20of%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fresources-and-community%2Fmultiple-conditional-if%2Fm-p%2F1445505%23M875%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fresources-and-community%2Fmultiple-conditional-if%2Fm-p%2F1445505%23M875%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1445580%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20if%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1445580%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%20I'll%20pick%20it%20up%20over%20there%20with%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hello,

 

Is there a formula i can use for a multiple conditions, as i can not use "IF"?

 

Thank you.

7 Replies
Highlighted

@Asmaa950 

 

Why can you not use IF?

 

Are you aware of IFS? It covered multiple multiple conditions.

 

Tell us more what your situation is.

Highlighted

@Asmaa950 

 

It just occurred to me that you may not have been aware that you can nest one IF condition in another...that was how IF alone used to be for multiple conditions.

 

But IFS now is more efficient and more readable.

Highlighted

@mathetes

Thank you for your reply,

Yes you are right i do not have "IFS" formula in my excel version  

Highlighted

@mathetesthis formula does not work

 

=IF(V10<15000/12,0,IF(V10<30000/12,"(V10-(15000/12))*2.5%",IF(V10<45000/12,"((V10-(30000/12))*10%)+(375/12)",IF(V10<60000/12,((V10-(45000/12))*15%)+(1875/12),IF(V10<200000/12,((V10-(60000/12))*20%)+(4125/12),IF(V10<400000/12,((V10-(200000/12))*22.5%)+(32125/12),IF(V10<600000/12,"((V10-(400000/12))*25%)+(77125/12)",IF(V10<700000/12,"((V10-(400000/12))*25%)+(77500/12)",IF(V10<800000/12,((V10-(400000/12))*25%)+(79750/12),IF(V10<900000/12,((V10-(400000/12))*25%)+(82000/12),IF(V10<1000000/12,((V10-(400000/12))*25%)+(85000/12),IF(V10>1000000/12,((V10-(400000/12))*25%)+(90000/12),0))))))))))))

Highlighted

@Asmaa950 

 

When a formula like that doesn't work--and "doesn't work" doesn't tell us what does happen, what error message you're getting, or is it not an error message at all, just not the result that you expect?--I will usually take each individual condition and ensure first that it works separately.

 

For the most part, just for intelligibility, we're better served by not trying to build nested formulas quite so deeply nested. Most texts on Excel warn against them. It's fun to make them work, perhaps, but trying to go back and diagnose or fix when it "doesn't work" is always frustrating.

 

I can't test the formula without the full sheet, and (frankly) I'm not going to devote the time to try to parse it. You clearly know the syntax of IF; it's not likely the case that IF per se isn't working, more that there's some relationship in there that causes failure...breaking it apart into separate IFs is what I'd recommend you do.

Highlighted
Highlighted

@Sergei Baklan 

 

Thanks. I'll pick it up over there with you.