| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function
Am trying to create a formula that will tell me: if the range of cells has
the single letter "R" or the single letter "T" in the individuals cells, it will return the word "Pass", or otherwise will return the word "Fail". Thanks, -- Michelle |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with IF function
=IF(SUM(COUNTIF(A1:A20,{"R";"T"}))0,"Pass","Fail" )
replace A1:A20 with your range -- Regards, Peo Sjoblom "Michelle" wrote in message ... Am trying to create a formula that will tell me: if the range of cells has the single letter "R" or the single letter "T" in the individuals cells, it will return the word "Pass", or otherwise will return the word "Fail". Thanks, -- Michelle |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with IF function
I'm assuming you mean if *any* cell in the range contains a R or T.
Assume the range in question is A1:A10... =IF(SUM(COUNTIF(A1:A10,{"R","T"})),"Pass","Fail") If *every* cell in the range is empty the result of the formula will be Fail. Don't know if that's what you want. -- Biff Microsoft Excel MVP "Michelle" wrote in message ... Am trying to create a formula that will tell me: if the range of cells has the single letter "R" or the single letter "T" in the individuals cells, it will return the word "Pass", or otherwise will return the word "Fail". Thanks, -- Michelle |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with IF function
Thank you for your replies, but I obviously didn't make myself clear. Am
trying to create a formula whe if the range of cells has the letter "R" or "T" in the cells, then the result will be "pass", but if any cell is blank or has a different letter in it, then the result will be "fail". Thanks, -- Michelle "T. Valko" wrote: I'm assuming you mean if *any* cell in the range contains a R or T. Assume the range in question is A1:A10... =IF(SUM(COUNTIF(A1:A10,{"R","T"})),"Pass","Fail") If *every* cell in the range is empty the result of the formula will be Fail. Don't know if that's what you want. -- Biff Microsoft Excel MVP "Michelle" wrote in message ... Am trying to create a formula that will tell me: if the range of cells has the single letter "R" or the single letter "T" in the individuals cells, it will return the word "Pass", or otherwise will return the word "Fail". Thanks, -- Michelle |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with IF function
OK, just need a slight modification:
=IF(SUM(COUNTIF(A1:A10,{"R","T"}))=ROWS(A1:A10),"P ass","Fail") -- Biff Microsoft Excel MVP "Michelle" wrote in message ... Thank you for your replies, but I obviously didn't make myself clear. Am trying to create a formula whe if the range of cells has the letter "R" or "T" in the cells, then the result will be "pass", but if any cell is blank or has a different letter in it, then the result will be "fail". Thanks, -- Michelle "T. Valko" wrote: I'm assuming you mean if *any* cell in the range contains a R or T. Assume the range in question is A1:A10... =IF(SUM(COUNTIF(A1:A10,{"R","T"})),"Pass","Fail") If *every* cell in the range is empty the result of the formula will be Fail. Don't know if that's what you want. -- Biff Microsoft Excel MVP "Michelle" wrote in message ... Am trying to create a formula that will tell me: if the range of cells has the single letter "R" or the single letter "T" in the individuals cells, it will return the word "Pass", or otherwise will return the word "Fail". Thanks, -- Michelle |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with IF function
This array-entered** formula appears to work...
=IF(COUNT(--FIND(A1:A10,"RT"))=COUNTA(A1:A10),"Pass","Fail") ** Commit the formula using Ctrl+Shift+Enter, not just Enter by itself Rick "Michelle" wrote in message ... Thank you for your replies, but I obviously didn't make myself clear. Am trying to create a formula whe if the range of cells has the letter "R" or "T" in the cells, then the result will be "pass", but if any cell is blank or has a different letter in it, then the result will be "fail". Thanks, -- Michelle "T. Valko" wrote: I'm assuming you mean if *any* cell in the range contains a R or T. Assume the range in question is A1:A10... =IF(SUM(COUNTIF(A1:A10,{"R","T"})),"Pass","Fail") If *every* cell in the range is empty the result of the formula will be Fail. Don't know if that's what you want. -- Biff Microsoft Excel MVP "Michelle" wrote in message ... Am trying to create a formula that will tell me: if the range of cells has the single letter "R" or the single letter "T" in the individuals cells, it will return the word "Pass", or otherwise will return the word "Fail". Thanks, -- Michelle |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with IF function
Thank you, but the formula doesn't give me the correct result. If I have
either "T" or "R" in all the cells, it gives me a "Fail" result, when I need a "Pass" result. -- Michelle "T. Valko" wrote: OK, just need a slight modification: =IF(SUM(COUNTIF(A1:A10,{"R","T"}))=ROWS(A1:A10),"P ass","Fail") -- Biff Microsoft Excel MVP "Michelle" wrote in message ... Thank you for your replies, but I obviously didn't make myself clear. Am trying to create a formula whe if the range of cells has the letter "R" or "T" in the cells, then the result will be "pass", but if any cell is blank or has a different letter in it, then the result will be "fail". Thanks, -- Michelle "T. Valko" wrote: I'm assuming you mean if *any* cell in the range contains a R or T. Assume the range in question is A1:A10... =IF(SUM(COUNTIF(A1:A10,{"R","T"})),"Pass","Fail") If *every* cell in the range is empty the result of the formula will be Fail. Don't know if that's what you want. -- Biff Microsoft Excel MVP "Michelle" wrote in message ... Am trying to create a formula that will tell me: if the range of cells has the single letter "R" or the single letter "T" in the individuals cells, it will return the word "Pass", or otherwise will return the word "Fail". Thanks, -- Michelle |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with IF function
Don't use this formula... it has a flaw in it.
Rick "Rick Rothstein (MVP - VB)" wrote in message ... This array-entered** formula appears to work... =IF(COUNT(--FIND(A1:A10,"RT"))=COUNTA(A1:A10),"Pass","Fail") ** Commit the formula using Ctrl+Shift+Enter, not just Enter by itself Rick "Michelle" wrote in message ... Thank you for your replies, but I obviously didn't make myself clear. Am trying to create a formula whe if the range of cells has the letter "R" or "T" in the cells, then the result will be "pass", but if any cell is blank or has a different letter in it, then the result will be "fail". Thanks, -- Michelle "T. Valko" wrote: I'm assuming you mean if *any* cell in the range contains a R or T. Assume the range in question is A1:A10... =IF(SUM(COUNTIF(A1:A10,{"R","T"})),"Pass","Fail") If *every* cell in the range is empty the result of the formula will be Fail. Don't know if that's what you want. -- Biff Microsoft Excel MVP "Michelle" wrote in message ... Am trying to create a formula that will tell me: if the range of cells has the single letter "R" or the single letter "T" in the individuals cells, it will return the word "Pass", or otherwise will return the word "Fail". Thanks, -- Michelle |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with IF function
Works perfectly! Thank you all for your help.
-- Michelle "Rick Rothstein (MVP - VB)" wrote: This array-entered** formula appears to work... =IF(COUNT(--FIND(A1:A10,"RT"))=COUNTA(A1:A10),"Pass","Fail") ** Commit the formula using Ctrl+Shift+Enter, not just Enter by itself Rick "Michelle" wrote in message ... Thank you for your replies, but I obviously didn't make myself clear. Am trying to create a formula whe if the range of cells has the letter "R" or "T" in the cells, then the result will be "pass", but if any cell is blank or has a different letter in it, then the result will be "fail". Thanks, -- Michelle "T. Valko" wrote: I'm assuming you mean if *any* cell in the range contains a R or T. Assume the range in question is A1:A10... =IF(SUM(COUNTIF(A1:A10,{"R","T"})),"Pass","Fail") If *every* cell in the range is empty the result of the formula will be Fail. Don't know if that's what you want. -- Biff Microsoft Excel MVP "Michelle" wrote in message ... Am trying to create a formula that will tell me: if the range of cells has the single letter "R" or the single letter "T" in the individuals cells, it will return the word "Pass", or otherwise will return the word "Fail". Thanks, -- Michelle |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with IF function
No it doesn't... there is a flaw in it. It will show a pass if there is the
same number of empty cells as there are characters that are not an R or T. Rick "Michelle" wrote in message ... Works perfectly! Thank you all for your help. -- Michelle "Rick Rothstein (MVP - VB)" wrote: This array-entered** formula appears to work... =IF(COUNT(--FIND(A1:A10,"RT"))=COUNTA(A1:A10),"Pass","Fail") ** Commit the formula using Ctrl+Shift+Enter, not just Enter by itself Rick "Michelle" wrote in message ... Thank you for your replies, but I obviously didn't make myself clear. Am trying to create a formula whe if the range of cells has the letter "R" or "T" in the cells, then the result will be "pass", but if any cell is blank or has a different letter in it, then the result will be "fail". Thanks, -- Michelle "T. Valko" wrote: I'm assuming you mean if *any* cell in the range contains a R or T. Assume the range in question is A1:A10... =IF(SUM(COUNTIF(A1:A10,{"R","T"})),"Pass","Fail") If *every* cell in the range is empty the result of the formula will be Fail. Don't know if that's what you want. -- Biff Microsoft Excel MVP "Michelle" wrote in message ... Am trying to create a formula that will tell me: if the range of cells has the single letter "R" or the single letter "T" in the individuals cells, it will return the word "Pass", or otherwise will return the word "Fail". Thanks, -- Michelle |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with IF function
Ok thanks for the warning.... was looking good!
-- Michelle "Rick Rothstein (MVP - VB)" wrote: Don't use this formula... it has a flaw in it. Rick "Rick Rothstein (MVP - VB)" wrote in message ... This array-entered** formula appears to work... =IF(COUNT(--FIND(A1:A10,"RT"))=COUNTA(A1:A10),"Pass","Fail") ** Commit the formula using Ctrl+Shift+Enter, not just Enter by itself Rick "Michelle" wrote in message ... Thank you for your replies, but I obviously didn't make myself clear. Am trying to create a formula whe if the range of cells has the letter "R" or "T" in the cells, then the result will be "pass", but if any cell is blank or has a different letter in it, then the result will be "fail". Thanks, -- Michelle "T. Valko" wrote: I'm assuming you mean if *any* cell in the range contains a R or T. Assume the range in question is A1:A10... =IF(SUM(COUNTIF(A1:A10,{"R","T"})),"Pass","Fail") If *every* cell in the range is empty the result of the formula will be Fail. Don't know if that's what you want. -- Biff Microsoft Excel MVP "Michelle" wrote in message ... Am trying to create a formula that will tell me: if the range of cells has the single letter "R" or the single letter "T" in the individuals cells, it will return the word "Pass", or otherwise will return the word "Fail". Thanks, -- Michelle |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with IF function
It works for me. Post the *exact* formula you used.
If all cells contain a R or if all cells contain a T or if all cells contain either a R or a T the formula returns Pass. If there are any empty cells or any entry other than a R or T the formula returns Fail. -- Biff Microsoft Excel MVP "Michelle" wrote in message ... Thank you, but the formula doesn't give me the correct result. If I have either "T" or "R" in all the cells, it gives me a "Fail" result, when I need a "Pass" result. -- Michelle "T. Valko" wrote: OK, just need a slight modification: =IF(SUM(COUNTIF(A1:A10,{"R","T"}))=ROWS(A1:A10),"P ass","Fail") -- Biff Microsoft Excel MVP "Michelle" wrote in message ... Thank you for your replies, but I obviously didn't make myself clear. Am trying to create a formula whe if the range of cells has the letter "R" or "T" in the cells, then the result will be "pass", but if any cell is blank or has a different letter in it, then the result will be "fail". Thanks, -- Michelle "T. Valko" wrote: I'm assuming you mean if *any* cell in the range contains a R or T. Assume the range in question is A1:A10... =IF(SUM(COUNTIF(A1:A10,{"R","T"})),"Pass","Fail") If *every* cell in the range is empty the result of the formula will be Fail. Don't know if that's what you want. -- Biff Microsoft Excel MVP "Michelle" wrote in message ... Am trying to create a formula that will tell me: if the range of cells has the single letter "R" or the single letter "T" in the individuals cells, it will return the word "Pass", or otherwise will return the word "Fail". Thanks, -- Michelle |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with IF function
It works on my machine.
"Rick Rothstein (MVP - VB)" wrote: No it doesn't... there is a flaw in it. It will show a pass if there is the same number of empty cells as there are characters that are not an R or T. Rick "Michelle" wrote in message ... Works perfectly! Thank you all for your help. -- Michelle "Rick Rothstein (MVP - VB)" wrote: This array-entered** formula appears to work... =IF(COUNT(--FIND(A1:A10,"RT"))=COUNTA(A1:A10),"Pass","Fail") ** Commit the formula using Ctrl+Shift+Enter, not just Enter by itself Rick "Michelle" wrote in message ... Thank you for your replies, but I obviously didn't make myself clear. Am trying to create a formula whe if the range of cells has the letter "R" or "T" in the cells, then the result will be "pass", but if any cell is blank or has a different letter in it, then the result will be "fail". Thanks, -- Michelle "T. Valko" wrote: I'm assuming you mean if *any* cell in the range contains a R or T. Assume the range in question is A1:A10... =IF(SUM(COUNTIF(A1:A10,{"R","T"})),"Pass","Fail") If *every* cell in the range is empty the result of the formula will be Fail. Don't know if that's what you want. -- Biff Microsoft Excel MVP "Michelle" wrote in message ... Am trying to create a formula that will tell me: if the range of cells has the single letter "R" or the single letter "T" in the individuals cells, it will return the word "Pass", or otherwise will return the word "Fail". Thanks, -- Michelle |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with IF function
Try filling in A1:A10 with this combination...
A1: <Leave empty A2: X A3:A10 Any combination of Rs and Ts for one of many ways to make the formula return a Pass when it shouldn't. It appears that FIND will find any string of characters inside the empty string. With A11 empty, =FIND(A11,"Any text") will return 1 as an answer. You can also see this without referencing an empty cell... =FIND("","Any text") Anyway, with or without the double unary, because of this, the COUNT function in my formula counts a hit here but COUNTA doesn't. So, a single non-hit by COUNTA (the X in my above example) is balanced out by my COUNT function's hit on the empty cell. Rick "Teethless mama" wrote in message ... It works on my machine. "Rick Rothstein (MVP - VB)" wrote: No it doesn't... there is a flaw in it. It will show a pass if there is the same number of empty cells as there are characters that are not an R or T. Rick "Michelle" wrote in message ... Works perfectly! Thank you all for your help. -- Michelle "Rick Rothstein (MVP - VB)" wrote: This array-entered** formula appears to work... =IF(COUNT(--FIND(A1:A10,"RT"))=COUNTA(A1:A10),"Pass","Fail") ** Commit the formula using Ctrl+Shift+Enter, not just Enter by itself Rick "Michelle" wrote in message ... Thank you for your replies, but I obviously didn't make myself clear. Am trying to create a formula whe if the range of cells has the letter "R" or "T" in the cells, then the result will be "pass", but if any cell is blank or has a different letter in it, then the result will be "fail". Thanks, -- Michelle "T. Valko" wrote: I'm assuming you mean if *any* cell in the range contains a R or T. Assume the range in question is A1:A10... =IF(SUM(COUNTIF(A1:A10,{"R","T"})),"Pass","Fail") If *every* cell in the range is empty the result of the formula will be Fail. Don't know if that's what you want. -- Biff Microsoft Excel MVP "Michelle" wrote in message ... Am trying to create a formula that will tell me: if the range of cells has the single letter "R" or the single letter "T" in the individuals cells, it will return the word "Pass", or otherwise will return the word "Fail". Thanks, -- Michelle |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with IF function
On Tuesday, August 26, 2008 at 4:18:00 AM UTC+2, Michelle wrote:
Am trying to create a formula that will tell me: if the range of cells has the single letter "R" or the single letter "T" in the individuals cells, it will return the word "Pass", or otherwise will return the word "Fail". Thanks, -- Michelle IF(A1:A20< 10,"FAIL,"PASS") What is wrong with this formula? Please help. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
| LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
| Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
| Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
| Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions | |||
Linear Mode
