Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Make table query will work in datasheet view but will not make tab | Excel Discussion (Misc queries) | |||
Cant make it work | New Users to Excel | |||
can't make it work, I need help | Excel Worksheet Functions | |||
Spin button in a work sheet - how do I make it work? | Excel Worksheet Functions | |||
how to make this work if sum=5+n2 then sum becomes the value of s. | Excel Discussion (Misc queries) |