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

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

Martin
Highlighted

@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