SOLVED

New Contributor

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

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

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

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

What exactly do you want to multiply?

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

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

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

=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,"?")

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

This was awesome! It worked!!