SOLVED
Home

Multiple IF's and drop down menu

%3CLINGO-SUB%20id%3D%22lingo-sub-826103%22%20slang%3D%22en-US%22%3EMultiple%20IF's%20and%20drop%20down%20menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-826103%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20a%20novice%20in%20Excel%20and%20have%20an%20issue%20that%20I%20can't%20seem%20to%20solve%20myself%20-%20I%20am%20lost.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECase%3A%3C%2FP%3E%3CP%3EDrop%20down%20menu%20with%203%20products%20cell%20(B17).%26nbsp%3BEach%20of%20these%20products%20have%20different%20conditions.%3C%2FP%3E%3CP%3E%22200%20kg%22%3C%2FP%3E%3CP%3E%22275%20kg%22%3C%2FP%3E%3CP%3E%22400%20kg%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECell%20(C28)%20shows%20length%20of%20rail%2C%20which%20is%20determined%20by%20input%20data.%20I%20got%20this%20one%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3ECell%20(C30)%20shows%20%22YES%22%20if%20reinforcement%20is%20needed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20if%20choosing%20%22200%20kg%22%20from%20the%20drop%20down%20menu%20and%20if%20C28%20exceeds%203200%20then%20C30%20must%20show%20%22YES%22%20to%20reinforcement%20of%20rail.%3CBR%20%2F%3EIf%20choosing%20%22275%20kg%22%20from%20the%20drop%20down%20menu%20and%20if%20C28%20exceeds%202750%20then%20C30%20must%20show%20%22YES%22%20to%20reinforcement%20of%20rail.%3CBR%20%2F%3EIf%20choosing%20%22400%20kg%22%20from%20the%20drop%20down%20menu%20and%20if%20C28%20exceeds%202250%20then%20C30%20must%20show%20%22YES%22%20to%20reinforcement%20of%20rail.%3CBR%20%2F%3E%3CBR%20%2F%3EMy%20formula%20looks%20like%20this%2C%20but%20something%20is%20wrong.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3DIF(B17%3D%22200%20kg%22%3BIF(C28%26gt%3B3200%3B%22YES%22%3B%22NO%22%3DIF(B17%3D%22275%20kg%22%3BIF(C28%26gt%3B2750%3B%22YES%22%3B%22NO%22%3DIF(B17%3D%22400%20kg%22%3BIF(C28%26gt%3B2250%3B%22YES%22%3B%22NO%22))))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20really%20appreciate%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%2C%3C%2FP%3E%3CP%3EMartin%20Mayland%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-826103%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-826594%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20IF's%20and%20drop%20down%20menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-826594%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F399425%22%20target%3D%22_blank%22%3E%40Mayland76%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Martin%3C%2FP%3E%3CP%3EI%20created%20a%20function%20for%20you%20that%20meets%20your%20combinations%20for%20the%203%20weights.%3C%2FP%3E%3CP%3ENote%3A%20the%20numbers%20in%20cell%20C28%20%3CSTRONG%3ESHOULD%20NOT%3C%2FSTRONG%3Ehave%20Kg%20in%20the%20same%20cell.%20Otherwise%2C%20you%20cannot%20use%20them%20in%20calculations.%20What%20you%20see%20in%20my%20attached%20file%20is%20custom%20formatting%20stored%20in%20memory.%3C%2FP%3E%3CP%3EThe%20function%20created%20in%20Cell%20%3CSTRONG%3EC30%3C%2FSTRONG%3Eis%3A%3CBR%20%2F%3E%3CSTRONG%3E%3DIF(%3C%2FSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3EOR%3C%2FSTRONG%3E%3C%2FFONT%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3E(%3C%2FFONT%3E%3CFONT%20color%3D%22%230000FF%22%3EAND(C17%3D200%2CC28%26gt%3B3200)%3C%2FFONT%3E%2C%3CFONT%20color%3D%22%23339966%22%3EAND(C17%3D275%2CC28%26gt%3B2750)%3C%2FFONT%3E%2C%3CFONT%20color%3D%22%23666699%22%3EAND(C17%3D400%2CC28%26gt%3B2250)%3C%2FFONT%3E%3CFONT%20color%3D%22%23FF0000%22%3E)%3C%2FFONT%3E%2C%22YES%22%2C%22NO%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EPlease%20look%20at%20the%20attached%20file.%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-826771%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20IF's%20and%20drop%20down%20menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-826771%22%20slang%3D%22en-US%22%3EHi%20Nabil%3CBR%20%2F%3EThank%20you%20so%20much%2C%20it%20works.%3CBR%20%2F%3EYou%20are%20now%20officially%20my%20new%20Excel-hero%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3E%3CBR%20%2F%3EMartin%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-827055%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20IF's%20and%20drop%20down%20menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-827055%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F399425%22%20target%3D%22_blank%22%3E%40Mayland76%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20nice%20words.%20I'm%20glad%20I%20could%20help.%3C%2FP%3E%3CP%3Eyou%20can%20join%20over%2020K%20Excel%20lovers%20learning%20Excel%20topics%20from%20me%20on%20my%20YouTube%20Channel%3CBR%20%2F%3EYouTube.com%2FOfficeInstructor%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20of%20Luck%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E
Mayland76
New Contributor

Hello

 

I am a novice in Excel and have an issue that I can't seem to solve myself - I am lost.

 

Case:

Drop down menu with 3 products cell (B17). Each of these products have different conditions.

"200 kg"

"275 kg"

"400 kg"

 

Cell (C28) shows length of rail, which is determined by input data. I got this one

Cell (C30) shows "YES" if reinforcement is needed.

 

So, if choosing "200 kg" from the drop down menu and if C28 exceeds 3200 then C30 must show "YES" to reinforcement of rail.
If choosing "275 kg" from the drop down menu and if C28 exceeds 2750 then C30 must show "YES" to reinforcement of rail.
If choosing "400 kg" from the drop down menu and if C28 exceeds 2250 then C30 must show "YES" to reinforcement of rail.

My formula looks like this, but something is wrong. 

=IF(B17="200 kg";IF(C28>3200;"YES";"NO"=IF(B17="275 kg";IF(C28>2750;"YES";"NO"=IF(B17="400 kg";IF(C28>2250;"YES";"NO"))))))

 

I really appreciate your help.

 

Best regards,

Martin Mayland

 

3 Replies
Solution

@Mayland76 

Hi Martin

I created a function for you that meets your combinations for the 3 weights.

Note: the numbers in cell C28 SHOULD NOT have Kg in the same cell. Otherwise, you cannot use them in calculations. What you see in my attached file is custom formatting stored in memory.

The function created in Cell C30 is:
=IF(OR(AND(C17=200,C28>3200),AND(C17=275,C28>2750),AND(C17=400,C28>2250)),"YES","NO")

Please look at the attached file.

Hope that helps

Nabil Mourad

Hi Nabil
Thank you so much, it works.
You are now officially my new Excel-hero

Martin

@Mayland76 

Thank you for your nice words. I'm glad I could help.

you can join over 20K Excel lovers learning Excel topics from me on my YouTube Channel
YouTube.com/OfficeInstructor

 

Best of Luck

Nabil Mourad

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 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