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.

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

"Natalie" wrote in message
...
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.

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)


"Natalie" wrote in message
...
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.

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

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

Thx Hrln, u hv a way w brvty <g

On Tue, 8 Nov 2005 13:55:03 -0800, "Natalie"
wrote:

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

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}))