Looking for a way to make this work.
If a lookup table is allowed......
Here's an example:
A1:E5 contains this list:
__0____27______40______46______Score
__0_____0_______0_______0______Fail
135___110______88______65______3rd
175___150_____125______99______2nd
225___200_____175_____149______1st
Then, all you need is this formula:
=INDEX(E2:E5,MATCH(N8,OFFSET(A2:D5,0,MATCH(G8,A1:E 1,1)-1,,1),1))
Does that help?
--------------------------
Regards,
Ron (XL2003, Win XP)
Microsoft MVP (Excel)
"Ron Coderre" wrote in message
...
Well....for a single-formula, no-lookup-table approach....
try this:
=CHOOSE(MATCH(N8,CHOOSE(MATCH(G8,{0,27,40,46},1),{ 0,135,175,225},{0,110,150,200},{0,88,125,175},{0,6 5,99,179}),1),"Fail","3rd","2nd","1st")
Is that something you can work with?
--------------------------
Regards,
Ron (XL2003, Win XP)
Microsoft MVP (Excel)
"Darryl_Neeley" wrote in message
...
It's a pretty long formula for this one cell, which I am trying to make
work.
Basically, I want it to where if there is nothing in the preceding cell,
for
this cell to display nothing. However, I believe I have one too many
functions in the cell to be able to add the necessary extra IF function.
I'm trying to turn this:
=IF(G8<27,IF(N8<135,"Fail",IF(N8225,"1st",IF(N81 75,"2nd",IF(N8134,"3rd","Fail")))),IF(G8<40,IF(N8 <110,"Fail",IF(N8200,"1st",IF(N8150,"2nd",IF(N8 109,"3rd","Fail")))),IF(G8<46,IF(N8<88,"Fail",IF(N 8175,"1st",IF(N8125,"2nd",IF(N887,"3rd","Fail") ))),IF(G845,IF(N8<65,"Fail",IF(N8149,"1st",IF(N8 99,"2nd",IF(N864,"3rd","Fail"))))))))
into this:
=IF(N8="","",IF(G8<27,IF(N8<135,"Fail",IF(N8225," 1st",IF(N8175,"2nd",IF(N8134,"3rd","Fail")))),IF (G8<40,IF(N8<110,"Fail",IF(N8200,"1st",IF(N8150, "2nd",IF(N8109,"3rd","Fail")))),IF(G8<46,IF(N8<88 ,"Fail",IF(N8175,"1st",IF(N8125,"2nd",IF(N887," 3rd","Fail")))),IF(G845,IF(N8<65,"Fail",IF(N8149 ,"1st",IF(N899,"2nd",IF(N864,"3rd","Fail"))))))) ))
It always highlights the last IF function, which leads me to believe that
it
is the one IF function too many.
Any ways I could get around this, right offhand? I thought of possibly
making a table in another sheet, but I'm trying to avoid that if
possible.
Thanks for the help.
|