SOLVED

Help with (if or) formula in Excel with 3 options (read below)

%3CLINGO-SUB%20id%3D%22lingo-sub-2370029%22%20slang%3D%22en-US%22%3EHelp%20with%20(if%20or)%20formula%20in%20Excel%20with%203%20options%20(read%20below)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2370029%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20Excel%20cell%20will%20have%203%20possible%20results......%20%22BLUE%22%2C%20%22RED%22%2C%20or%20if%20it%20is%20left%20Blank%2Fempty.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20BLUE%20is%20used%2C%20multiply%20by%202.%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20RED%20is%20used%2C%20just%20return%201%20(i.e.%20no%20math%20function%2C%20just%20use%201).%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20the%20cell%20is%20Blank%2C%20as%20in%20nothing%20is%20typed%2C...it%20is%20default%20clear%2C%20I%20want%20it%20to%20either%20remain%20blank%2C%20or%20if%20there%20needs%20to%20be%20a%20numeric%20value%2C%20use%200.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF((OR(A1%3D%22BLUE%22%2C%22RED%22))%2CA1*2%2C1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20get%20the%20formula%20to%20return%20a%20Zero%20(0)%20if%20the%20cell%20is%20blank%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2370029%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2370046%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20(if%20or)%20formula%20in%20Excel%20with%203%20options%20(read%20below)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2370046%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1058332%22%20target%3D%22_blank%22%3E%40TheFireman408%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20A1%20is%20%22BLUE%22%2C%20A1*2%20makes%20no%20sense%2C%20you%20cannot%20multiply%20a%20word%20with%202.%3C%2FP%3E%0A%3CP%3EWhat%20exactly%20do%20you%20want%20to%20multiply%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2370064%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20(if%20or)%20formula%20in%20Excel%20with%203%20options%20(read%20below)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2370064%22%20slang%3D%22en-US%22%3EColumn%20A%20%3D%20Color%20(Blue%2C%20Red%2C%20Blank)%3CBR%20%2F%3EColumn%20B%20%3D%20Number%20of%20locations%20(0%20-%20100)%3CBR%20%2F%3EColumn%20C%20%3D%20Formula%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20this%20could%20look%20like%20.....%3CBR%20%2F%3EA1%20%3D%20Blue%3CBR%20%2F%3EB1%20%3D%206%3CBR%20%2F%3EC1%20%3DIF((OR(A1%3D%22BLUE%22%2C%22RED%22))%2CB1*2%2C1)%3CBR%20%2F%3EC1%20would%20populate%20with%2012%3CBR%20%2F%3E%3CBR%20%2F%3EOr%20.....%3CBR%20%2F%3EA1%20%3D%20RED%3CBR%20%2F%3EB1%20%3D%206%3CBR%20%2F%3EC1%20%3DIF((OR(A1%3D%22BLUE%22%2C%22RED%22))%2CB1*2%2C1)%3CBR%20%2F%3EC1%20would%20populate%20with%201%3CBR%20%2F%3E%3CBR%20%2F%3EHELP%3A%3CBR%20%2F%3EOr%20.....%3CBR%20%2F%3EA1%20%3D%20(blank)%20(i.e.%20no%20entry)%3CBR%20%2F%3EB1%20%3D%206%3CBR%20%2F%3EC1%20%3DIF((OR(A1%3D%22BLUE%22%2C%22RED%22))%2CB1*2%2C1)%3CBR%20%2F%3EC1%20would%20populate%20with%200%20(or%20preferably%20remain%20blank%2Fempty%2C%20since%20A1%20is%20empty)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2370068%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20(if%20or)%20formula%20in%20Excel%20with%203%20options%20(read%20below)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2370068%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1058332%22%20target%3D%22_blank%22%3E%40TheFireman408%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(A1%3D%22%22%2C%22%22%2CIF(A1%3D%22RED%22%2C1%2CIF(A1%3D%22BLUE%22%2C2*B1%2C%22%3F%22)))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20Excel%202019%20or%20Excel%20in%20Microsoft%20365%2C%20you%20can%20also%20use%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFS(A1%3D%22%22%2C%22%22A1%3D%22RED%22%2C1%2CA1%3D%22BLUE%22%2C2*B1%2CTRUE%2C%22%3F%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

The Excel cell will have 3 possible results...... "BLUE", "RED", or if it is left Blank/empty.

 

When BLUE is used, multiply by 2.   

When RED is used, just return 1 (i.e. no math function, just use 1).   

When the cell is Blank, as in nothing is typed,...it is default clear, I want it to either remain blank, or if there needs to be a numeric value, use 0.

 

=IF((OR(A1="BLUE","RED")),A1*2,1)

 

How do I get the formula to return a Zero (0) if the cell is blank?

 

 

 

4 Replies

@TheFireman408 

 

If A1 is "BLUE", A1*2 makes no sense, you cannot multiply a word with 2.

What exactly do you want to multiply?

Column A = Color (Blue, Red, Blank)
Column B = Number of locations (0 - 100)
Column C = Formula

So this could look like .....
A1 = Blue
B1 = 6
C1 =IF((OR(A1="BLUE","RED")),B1*2,1)
C1 would populate with 12

Or .....
A1 = RED
B1 = 6
C1 =IF((OR(A1="BLUE","RED")),B1*2,1)
C1 would populate with 1

HELP:
Or .....
A1 = (blank) (i.e. no entry)
B1 = 6
C1 =IF((OR(A1="BLUE","RED")),B1*2,1)
C1 would populate with 0 (or preferably remain blank/empty, since A1 is empty)
best response confirmed by TheFireman408 (New Contributor)
Solution

@TheFireman408 

 

=IF(A1="","",IF(A1="RED",1,IF(A1="BLUE",2*B1,"?")))

 

If you have Excel 2019 or Excel in Microsoft 365, you can also use

 

=IFS(A1="",""A1="RED",1,A1="BLUE",2*B1,TRUE,"?")

This was awesome! It worked!!