Formula To Calculate Combinations

honkin

Board Regular
Joined
Mar 20, 2012
Messages
145
Office Version
  1. 2016
Platform
  1. MacOS
I need to put a formula together which does the following, but am struggling.

Here is what is required.

Columns U, V & W have either an X or are blank. I want to put the result of varying combinations of the Xs in another column.

So In English, IF W2 =X, then the result will be x. If U2 & V2 both have an X, the result will be **, otherwise the result will be *

Just to clarify, if there is an X in column W, the will never be an X in U or V. If there is an X in either U or V, there will never be an X in W.

Only 3 possibly scenarios exist

1) U2=X, V2=X, W2=Blank - Result =**
2) U2=X, V2=Blank, W2=Blank - Result = *
3) U2=Blank, V2=Blank, W2=X - Result = x

I tried starting with
VBA Code:
=IF(W2="X",x,IF(AND(U2="X",V2="X"),"**","*"))
but the result gives me #NAME? I thought this was saying IF W2 = "X" put x, IF U2="X" AND V2="X" then put "**", otherwise put "*"

Any help gladly accepted

cheers
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,326
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
The lower case x needs quote marks around it. If(W2="X","x" .....
 

honkin

Board Regular
Joined
Mar 20, 2012
Messages
145
Office Version
  1. 2016
Platform
  1. MacOS
ah cheers JoeMo. Had looked at it for so long and just couldn't see it.

Thanks again
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,326
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You are welcome - thanks for the reply.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,478
Messages
5,548,268
Members
410,825
Latest member
Dave12
Top