Thanks Mark W, but still not working
Posted by Cliff on December 18, 2001 8:16 AM
I'm sure I'm not explaining it right. The formulas you have given work great and I thank you for them. Now I have a new problem, can I use several (or) functions in a formula? I have five names that need to be put in a cell based on the contents of a cell. Like =if(or(h32=10:00, h32=11:00), "name", (h33=10:00, h33=11:00), "name", (h34=10:00, h34=11:00), "name", (h35=10:00, h35=11:00), "name"), I have tried this formula but only get####### as a result. I need for the formula above to check h32 to h36 for two to four times and return one of the five names if a cell matches the time.
Posted by Mark W. on December 18, 2001 8:27 AM
Cliff, I don't know what you're trying to accomplish.
You've changed your problem statement 3 times.
Provide a succinct description along with a
sample data set and desired results.
Posted by Cliff on December 18, 2001 9:44 AM
Sorry, I'll try to explain better. I need for one formula to do this: check cell h32, if it contains 10:00 or 11:00 then produce the name Cliff, if not then check cell h33, if that one contains 10:00 or 11:00 then produce the name Jamie, if that one doesn't, then check cell h34, if it contains 10:00 or 11:00 then produce the name Adam, if that one is false, then check cell h35, if it contains 10:00 or 11:00 then produce the name Fonda, if it doesn't then check cell h36, if it contains 10:00 or 11:00 then produce the name Kerwin, if none of the cells contain 8:00 or 10:00 then leave blank. So, I am trying to find the correct way to write the following formula: =if(h32=10:00, 11:00), "Cliff", if not then does (h33=10:00, 11:00), "Jamie", if not then does (h34=10:00, 11:00), "Adam", if not then does (h35=10:00, 11:00), "Fonda", if not then does (h36=10:00, 11:00), "Kerwin", if not then leave blank. I hope this explains it better.
Posted by Mark W. on December 18, 2001 12:37 PM
{=CHOOSE(MIN(IF((($H$32:$H$36=10/24)+($H$32:$H$36=11/24)),{1;2;3;4;5}))+1,"","Cliff","Jamie","Adam","Fonda","Kerwin")}
Note: This is an array formula which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.
Posted by Mark W. on December 18, 2001 12:45 PM
Went a little bonkers with parentheses...
...you can shed a pair...
{=CHOOSE(MIN(IF(($H$32:$H$36=10/24)+($H$32:$H$36=11/24),{1;2;3;4;5}))+1,"","Cliff","Jamie","Adam","Fonda","Kerwin")}
Posted by Mark W. on December 19, 2001 11:03 AM
One more variation...
{=CHOOSE(MAX(($H$32:$H$36=10/24)+($H$32:$H$36=11/24)*{5;4;3;2;1})+1,"","Kerwin","Fonda","Adam","Jamie","Cliff")}
...which eliminates the need for IF.
