Looking for a way to make this work.

[Guest]

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(N8>225,"1st",IF(N8>175,"2nd",IF(N8>134,"3rd","Fail")))),IF(G8<40,IF(N8<110,"Fail",IF(N8>200,"1st",IF(N8>150,"2nd",IF(N8>109,"3rd","Fail")))),IF(G8<46,IF(N8<88,"Fail",IF(N8>175,"1st",IF(N8>125,"2nd",IF(N8>87,"3rd","Fail")))),IF(G8>45,IF(N8<65,"Fail",IF(N8>149,"1st",IF(N8>99,"2nd",IF(N8>64,"3rd","Fail"))))))))

into this:

=IF(N8="","",IF(G8<27,IF(N8<135,"Fail",IF(N8>225,"1st",IF(N8>175,"2nd",IF(N8>134,"3rd","Fail")))),IF(G8<40,IF(N8<110,"Fail",IF(N8>200,"1st",IF(N8>150,"2nd",IF(N8>109,"3rd","Fail")))),IF(G8<46,IF(N8<88,"Fail",IF(N8>175,"1st",IF(N8>125,"2nd",IF(N8>87,"3rd","Fail")))),IF(G8>45,IF(N8<65,"Fail",IF(N8>149,"1st",IF(N8>99,"2nd",IF(N8>64,"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.

 

[Don Guillett]

Without looking too closely, first I would recommend better organization
such as
if(g8>45,1,if(g8>40,2,if(g8>27,3,4)))
then do the same for your 1,2,3,4

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
news:[email protected]...

 

[Ron Coderre]

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,65,99,179}),1),"Fail","3rd","2nd","1st")

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)


news:[email protected]...

 

[Guest]

I don't believe I know what you're talking about when you say "my 1,2,3,4."

Don't get me wrong, I know that the code is nothing more than a giant
cluster of information. I just don't know enough about the code on Excel to
be able to code it better.

You see, what I'm trying to get with this is, if your age is 17-26, this is
what your point values count as. If you are 27-39, this is what your scores
count as. If you are 40-45, this is what they count as. And if you are 46+,
that is what they count as.

I have two cells tied into this one.

 

[Ron Coderre]

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(A25,0,MATCH(G8,A1:E1,1)-1,,1),1))

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Guest]

Give this a try...........

=IF(N8="","",IF(G8<27,IF(N8<135,"Fail",IF(N8>225,"1st",IF(N8>175,"2nd",IF(N8>134,"3rd","Fail")))),IF(G8<40,IF(N8<110,"Fail",IF(N8>200,"1st",IF(N8>150,"2nd",IF(N8>109,"3rd","Fail")))),IF(G8<46,IF(N8<88,"Fail",IF(N8>175,"1st",IF(N8>125,"2nd",IF(N8>87,"3rd","Fail")))),IF(N8<65,"Fail",IF(N8>149,"1st",IF(N8>99,"2nd",IF(N8>64,"3rd","Fail"))))))))

Vaya con Dios,
Chuck, CABGx3

 

[David Biddulph]

That looks like a prime candidate for a LOOKUP function.

 

[Guest]

It worked. Thank you very much for your help.

Give this a try...........

=IF(N8="","",IF(G8<27,IF(N8<135,"Fail",IF(N8>225,"1st",IF(N8>175,"2nd",IF(N8>134,"3rd","Fail")))),IF(G8<40,IF(N8<110,"Fail",IF(N8>200,"1st",IF(N8>150,"2nd",IF(N8>109,"3rd","Fail")))),IF(G8<46,IF(N8<88,"Fail",IF(N8>175,"1st",IF(N8>125,"2nd",IF(N8>87,"3rd","Fail")))),IF(N8<65,"Fail",IF(N8>149,"1st",IF(N8>99,"2nd",IF(N8>64,"3rd","Fail"))))))))

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Glad it worked for you..........thanks for the feedback.

Vaya con Dios,
Chuck, CABGx3