Conditional tests

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

At present I am using 7 IF condition tests for the following case, for
example where P=pass & F=Failed

latest status col 2 col 3 col 4 col 5 ... .....
col 30
1 p p f
p
2 f f f
3 p f p
4

the "latest status" column contains the last text entry. The formula I am
using is of the format

=IF(V122<>"",V122,IF(U122<>"",U122,IF(T122<>"",T122,IF(S122<>"",S122,IF(R122<>"",R122,IF(Q122<>"",Q122,IF(P122<>"",P122,IF(O122<>"",O122))))))))

Is the any other way to extract the same information row by row case but not
be dependent upon the nestign limitation of 7

Many thanks in advance
 
Hi
try:
=LOKKUP(2,1/(1-ISBLANK(V122:AA122)),V122:AA122)
-----Original Message-----
At present I am using 7 IF condition tests for the following case, for
example where P=pass & F=Failed

latest status col 2 col 3 col 4 col 5 ... .....
col 30
1 p p f
p
2 f
Click to expand...
f f
 
Hi

It was too much for 30 conditions, but I think you can got the idea (example
formula cheks columns B:N, and I din't replace delimiters there)

=N2 & IF(COUNTIF(N2:N2;"<>")>0;"";M2) & IF(COUNTIF(M2:N2;"<>")>0;"";L2) &
IF(COUNTIF(L2:N2;"<>")>0;"";K2) & IF(COUNTIF(K2:N2;"<>")>0;"";J2) &
IF(COUNTIF(J2:N2;"<>")>0;"";I2) & IF(COUNTIF(I2:N2;"<>")>0;"";H2) &
IF(COUNTIF(H2:N2;"<>")>0;"";G2) & IF(COUNTIF(G2:N2;"<>")>0;"";F2) &
IF(COUNTIF(F2:N2;"<>")>0;"";E2) & IF(COUNTIF(E2:N2;"<>")>0;"";D2) &
IF(COUNTIF(D2:N2;"<>")>0;"";C2) & IF(COUNTIF(C2:N2;"<>")>0;"";B2)

My advice is to define every part of expression as named function, and use
them in your formula. This way the formula doesn't look so huge. Something
like this:
Select a cell in row 2
Define names
Test30=Sheet1!$AE2
Test29=IF(COUNTIF(Sheet1!$AE2:$AE2,"<>")>0,"",Sheet1!$AD2)
Test28=IF(COUNTIF(Sheet1!$AD2:$AE2,"<>")>0,"",Sheet1!$AC2)
Test27=IF(COUNTIF(Sheet1!$AC2:$AE2,"<>")>0,"",Sheet1!$AB2)
....
Test2=IF(COUNTIF(Sheet1!$D2:$AE2,"<>")>0,"",Sheet1!$C2)
Test1=IF(COUNTIF(Sheet1!$C2:$AE2,"<>")>0,"",Sheet1!$B2)

Now you can write the formula (into any row, it compares cells with test
results in same row) in form
=Test1 & Test2 & Test3 & .... & Test29 & Test30


--
Arvi Laanemets
(When sending e-mail, use address arvil<At>tarkon.ee)
 
=LOOKUP(REPT("z",255),A2:Z2)

would retrieve the last text value frm A2:Z2.

If you have formulas in A2:Z2 that return "", try:

=LOOKUP(2,1/(A2:Z2<>""),A2:Z2)
 
Back
Top