Looking for a way to make this work.
 

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.

Looking for a way to make this work.
 

Without looking too closely, first I would recommend better organization
such as
if(g845,1,if(g840,2,if(g827,3,4)))
then do the same for your 1,2,3,4

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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.

Looking for a way to make this work.
 

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.

Looking for a way to make this work.
 

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.

"Don Guillett" wrote:

Without looking too closely, first I would recommend better organization
such as
if(g845,1,if(g840,2,if(g827,3,4)))
then do the same for your 1,2,3,4

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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.

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.

Looking for a way to make this work.
 

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

=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(N8<65,"Fail",IF(N8149,"1st",IF (N899,"2nd",IF(N864,"3rd","Fail"))))))))

Vaya con Dios,
Chuck, CABGx3



"Darryl_Neeley" wrote:

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.

Looking for a way to make this work.
 

That looks like a prime candidate for a LOOKUP function.
--
David Biddulph

"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.

Looking for a way to make this work.
 

It worked. Thank you very much for your help.

"CLR" wrote:

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

=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(N8<65,"Fail",IF(N8149,"1st",IF (N899,"2nd",IF(N864,"3rd","Fail"))))))))

Vaya con Dios,
Chuck, CABGx3



"Darryl_Neeley" wrote:

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.

Looking for a way to make this work.
 

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

Vaya con Dios,
Chuck, CABGx3



"Darryl_Neeley" wrote:

It worked. Thank you very much for your help.

"CLR" wrote:

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

=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(N8<65,"Fail",IF(N8149,"1st",IF (N899,"2nd",IF(N864,"3rd","Fail"))))))))

Vaya con Dios,
Chuck, CABGx3



"Darryl_Neeley" wrote:

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.