multiple scenario forumla (maybe a nested if statement)

[sharann via OfficeKB.com]

I have a spreadsheet of possible hiring canidates that may or may not go
through 5 stages of the hiring process which are listed below.

I need to know for instance if a candidate made it through some stages or all
stages of the hiring process. Under the column headings (Attded Career
Session, Test Score, Chosen 4 1st interview, Chosen 4 2nd Interview, Hired)
the data will be listed but Im not sure how to say Canidate 1 made it through
all but the Chosen 2nd Intereview.

Canidate Attded Career Session, Test Score, Chosen 1st interview, Chosen
2nd Interview, Hired
1
2
3

Any help is greatly appreciated...thanks!

 

[Max]

Here's a simple way to extract the "latest" status for each candidate ..
Assume the 5 sequential stages are in cols B to F, labels in B1:F1, data in
row 2 down
Put in G2, normal ENTER to confirm will do:
=IF(COUNTA(B2:F2)=0,"",INDEX(B$1:F$1,,MATCH(TRUE,INDEX(B2:F2="",),0)))
Copy down as far as required. Col G will return the required "latest" stage
attained. The core working assumption is that data for each candidate will
be entered/filled in sequence from left to right, ie from "Attded Career
Session" stage to "Test Score" to "Chosen 1st interview" to "Chosen 2nd
Interview" and finally to "Hired". Data filled in for candidates can be
text, number or mixed data, it doesn't matter. Inspiring? wave it here

 

[sharann via OfficeKB.com]

Thanks Max for your response
One more thing please...is there anyway to return this in the formula:
Candidate 1 made it to Stage 1, 2, 3 (Attded Career Session",Test Score,
Chosen 1st interview) etc...basically need it to say what stages the
candidates made it through wheter it's Stage 1, 2, 3, or all 5 and so on for
the candidates...actually say in the cell where the formual is returning the
answer 'Stage 1' etc

Much appreciated!

Here's a simple way to extract the "latest" status for each candidate ..
Assume the 5 sequential stages are in cols B to F, labels in B1:F1, data in
row 2 down
Put in G2, normal ENTER to confirm will do:
=IF(COUNTA(B2:F2)=0,"",INDEX(B$1:F$1,,MATCH(TRUE,INDEX(B2:F2="",),0)))
Copy down as far as required. Col G will return the required "latest" stage
attained. The core working assumption is that data for each candidate will
be entered/filled in sequence from left to right, ie from "Attded Career
Session" stage to "Test Score" to "Chosen 1st interview" to "Chosen 2nd
Interview" and finally to "Hired". Data filled in for candidates can be
text, number or mixed data, it doesn't matter. Inspiring? wave it here

I have a spreadsheet of possible hiring canidates that may or may not go
through 5 stages of the hiring process which are listed below.

[quoted text clipped - 15 lines]

Any help is greatly appreciated...thanks!

 

[sharann via OfficeKB.com]

Thanks Max for your response
One more thing please...is there anyway to return this in the formula:
Candidate 1 made it to Stage 1, 2, 3 (Attded Career Session",Test Score,
Chosen 1st interview) etc...basically need it to say what stages the
candidates made it through wheter it's Stage 1, 2, 3, or all 5 and so on for
the candidates...actually say in the cell where the formual is returning the
answer 'Stage 1' etc

Much appreciated!

Here's a simple way to extract the "latest" status for each candidate ..
Assume the 5 sequential stages are in cols B to F, labels in B1:F1, data in
row 2 down
Put in G2, normal ENTER to confirm will do:
=IF(COUNTA(B2:F2)=0,"",INDEX(B$1:F$1,,MATCH(TRUE,INDEX(B2:F2="",),0)))
Copy down as far as required. Col G will return the required "latest" stage
attained. The core working assumption is that data for each candidate will
be entered/filled in sequence from left to right, ie from "Attded Career
Session" stage to "Test Score" to "Chosen 1st interview" to "Chosen 2nd
Interview" and finally to "Hired". Data filled in for candidates can be
text, number or mixed data, it doesn't matter. Inspiring? wave it here

I have a spreadsheet of possible hiring canidates that may or may not go
through 5 stages of the hiring process which are listed below.

[quoted text clipped - 15 lines]

Any help is greatly appreciated...thanks!

 

[sharann via OfficeKB.com]

Thanks Max for your response
One more thing please...is there anyway to return this in the formula:
Candidate 1 made it to Stage 1, 2, 3 (Attded Career Session",Test Score,
Chosen 1st interview) etc...basically need it to say what stages the
candidates made it through wheter it's Stage 1, 2, 3, or all 5 and so on for
the candidates...actually say in the cell where the formual is returning the
answer 'Stage 1' etc

Much appreciated!

Here's a simple way to extract the "latest" status for each candidate ..
Assume the 5 sequential stages are in cols B to F, labels in B1:F1, data in
row 2 down
Put in G2, normal ENTER to confirm will do:
=IF(COUNTA(B2:F2)=0,"",INDEX(B$1:F$1,,MATCH(TRUE,INDEX(B2:F2="",),0)))
Copy down as far as required. Col G will return the required "latest" stage
attained. The core working assumption is that data for each candidate will
be entered/filled in sequence from left to right, ie from "Attded Career
Session" stage to "Test Score" to "Chosen 1st interview" to "Chosen 2nd
Interview" and finally to "Hired". Data filled in for candidates can be
text, number or mixed data, it doesn't matter. Inspiring? wave it here

I have a spreadsheet of possible hiring canidates that may or may not go
through 5 stages of the hiring process which are listed below.

[quoted text clipped - 15 lines]

Any help is greatly appreciated...thanks!

 

[Max]

Sorry, think the earlier should have read as:
=IF(COUNTA(B2:F2)=0,"",INDEX(B$1:F$1,,MATCH(TRUE,INDEX(B2:F2="",),0)-1))

As for your desired output requested, its simple.
Just replace the labels in B1:F1 with the text that you want the formula to
return ie put in B1:F1 : Stage 1, Stage 2, ... Stage 5

 

[sharann via OfficeKB.com]

Thanks again for your help...much appreciated

Sorry, think the earlier should have read as:
=IF(COUNTA(B2:F2)=0,"",INDEX(B$1:F$1,,MATCH(TRUE,INDEX(B2:F2="",),0)-1))

As for your desired output requested, its simple.
Just replace the labels in B1:F1 with the text that you want the formula to
return ie put in B1:F1 : Stage 1, Stage 2, ... Stage 5

Thanks Max for your response
One more thing please...is there anyway to return this in the formula:

[quoted text clipped - 7 lines]

Much appreciated!

 

[Max]

Trust you are still following this thread. The earlier would have failed to
give the correct return for the rightmost: Stage 5

The correct expression should be:
=IF(COUNTA(B2:F2)=0,"",IF(COUNTA(B2:F2)=5,F$1,INDEX(B$1:F$1,,MATCH(TRUE,INDEX(B2:F2="",),0)-1)))

My apologies for the error