How do I set up an IF function with text and numbers?

[Guest]

I want a function that recognizes if there is the letter X in cell F7, then
K7 = 5; or if there is an X in cell G7 then K7 = 4, or if there is an X in H7
then K7 = 3 and so on.

 

[Biff]

Hi!

and so on.

"And so on" implies there are more conditions. How many?

Do this mean that ONLY ONE CELL may contain "X"?

If 2 or more cells contain "X", which cell has precedence?

Biff

 

[Bob Phillips]

In cell K7

=IF(F7="X",5,IF(G7="X",4,IF(H7="X",3,...)))

but there is a limit of 7 nested IFs.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[DOR]

If the 7 nested IFs ever present a problem, this formula can be
extended beyond 7 columns,

=IF(ISNA(MATCH("X",F7:J7,0)),"",COLUMNS(F7:J7)+1-MATCH("X",F7:J7,0)) in
K7

It chooses the result based on the first X encountered in the range
under test, reading from left to right.

HTH

 

[Harlan Grove]

DOR wrote...

If the 7 nested IFs ever present a problem, this formula can be
extended beyond 7 columns,

=IF(ISNA(MATCH("X",F7:J7,0)),"",COLUMNS(F7:J7)+1-MATCH("X",F7:J7,0)) in
K7

....

You could shorten this to

=IF(COUNTIF(F7:J7,"X"),COLUMNS(F7:J7)+1-MATCH("X",F7:J7,0),"")

 

[DOR]

Thx Hrln, u hv a way w brvty <g>

 

[Ron Rosenfeld]

I want a function that recognizes if there is the letter X in cell F7, then
K7 = 5; or if there is an X in cell G7 then K7 = 4, or if there is an X in H7
then K7 = 3 and so on.

Assuming the leftmost column has priority if there are multiple X's, and
assuming the contents of the cell is only an X (and not, for example, eXcite),
then:

K7: =CHOOSE(MATCH("X",F7:F7:J7,0),5,4,3,2,1)


--ron

 

[Harlan Grove]

Ron Rosenfeld wrote...
....

Assuming the leftmost column has priority if there are multiple X's, and
assuming the contents of the cell is only an X (and not, for example, eXcite),
then:

K7: =CHOOSE(MATCH("X",F7:F7:J7,0),5,4,3,2,1)

Yeah, but it's effectively hardcoded. insert columns between F and J
and put the leftmost X in the 6th column.

If hardcoding, zero return value for no X in the range, and array
formulas were OK,

=MAX(IF(F7:J7="X",{5,4,3,2,1}))