Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help to write a formula using a letter value and a number value
I need to write a formula that looks at a set of cells and determines if they
equal "F" or if they have numerical value of less than 59 and/or greater than 1. How would I write this? If "F" does not appear in any of the cells, then I want it to equal "Z". If the numerical value is 0 or greater than 59, I want it to equal "Z" as well. Thank you for your help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help to write a formula using a letter value and a number value
Assuming your letters are in A1 and numbers in B1, enter this formula
in C1: =IF(OR(A1<"F",B1=0,B159)"Z",IF(AND(A1="F",B11,B 1<59),"yes","not specified") You don't specify what you want the formula to do if the conditions are met, so this outputs "yes". Your definition of requirements is a bit loose (eg what happens when B1 = 1, or less than 0?). You don't say what you want to do when the conditions are not met, so this outputs "not specified". Hope this helps. Pete |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help to write a formula using a letter value and a number value
Pete:
In the meantime, prior to getting your post, I wrote the following formula: =IF(Q4:U4="F",A4),IF(Q4:U4<60,A4),IF(Q4:U459,"Z") ,IF(Q4:U4=0,"Z") Why won't this work? Cells Q-U have either an "F", "D" or "D-" OR a number from 0-59. For each cell Q through U, if it is an F, D, D- or number between 1-59, I want it to refer to the name in column A (in this case A4). If it is a number less than 1 (0) then I want it to ="Z". If it is NOT an F, D or D- or a number above 59, I also want it to = "Z". Why won't my formula above work. I just tried the one you posted and it is not working. Thanks so much for your help. "Ikeagle" wrote: I need to write a formula that looks at a set of cells and determines if they equal "F" or if they have numerical value of less than 59 and/or greater than 1. How would I write this? If "F" does not appear in any of the cells, then I want it to equal "Z". If the numerical value is 0 or greater than 59, I want it to equal "Z" as well. Thank you for your help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help to write a formula using a letter value and a number value
Where are you putting this formula? You're looking for 5 results from 5 cells Q4:U4, do you want results in 5 different cells? This should work for one cell =IF(ISNUMBER(Q4),IF(OR(Q4=0,Q459),"Z",A4),A4) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=506058 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|