MrExcel Publishing
Your One Stop for Excel Tips & Solutions

If Function


Posted by Rob on March 02, 2001 1:45 PM

I would like to have a formula search a defined range of cells and return a "Yes" or "No" answer in cell A1. The defined range is from B25:M25. The range includes dates and test scores. For instance, cell B25 will be "3/5/01" and cell C25 will be 65, cell D25 will be "4/5/01" and cell E25 will be 68, and so on down the range... Some of the cells in the range will not have any data in them yet, this will be filled in as time goes on.
Cell A1 will have a "Yes" or "No" answer, if the defined range has a test score of greater then or equal to 70.
I am attempting to use the following formula in cell A1:
=If(B25:M25>=70,"Yes","No"), unfortunately this formula does not always work correctly. I think it is because Excel is interpreting the "dates" as general numbers and is throwing off the formula and providing a "Yes" answer in cell A1 when it should be "No".

Any help would be greatly appreciated.


Posted by Aladin Akyurek on March 02, 2001 3:42 PM

The formula =If(B25:M25>=70,"Yes","No") should return a #VALUE! error, unless you array-entered it.
As an array-formula it will not do the job the way you want it.
The condition part will produce an array of logical values. the formula will then give a result that will depend on the first logical value. That is in your case always Yes because B25 is a date which is represented internally as a serial number. That serial number makes the condition become true (it's a 5-digit number).

I would suggest to reorganize your data, e.g., all dates in a column and all corresponding scores in a separate column. You would easily check for a score >=70 by just looking in the column for scores, for example, with the COUNTIF function.

I would also suggest to have a look at the IF function under Help|Contents and Index.

Just to satisfy your curiosity, you can use the following array-formula in A1 to get a straight Yes or No, given the current organization of your data. The formula assumes that you don't have scores that are 5-digit big.

=IF(SUM((LEN(B25:M25)<>5)*(B25:M25>=70))>=1,"Yes","No")

You have to hit CONTROL+SHIF+ENTER to enter (not just ENTER) this formula.

Aladin

Posted by Rob on March 02, 2001 6:55 PM

If I rearrange the spreadsheet so the data is in colums, would I still be able to use the countif function to return a value such as "Yes" or "No"? I know it would give me the # of cells with a value greater than or equal to 70, but could it provide "Yes"?
Anyway thank you for the previous formula. It worked perfectly.

Posted by Aladin Akyurek on March 03, 2001 12:14 AM

: I would like to have a formula search a defined range of cells and return a "Yes" or "No" answer in cell A1. The defined range is from B25:M25. The range includes dates and test scores. For instance, cell B25 will be "3/5/01" and cell C25 will be 65, cell D25 will be "4/5/01" and cell E25 will be 68, and so on down the range... Some of the cells in the range will not have any data in them yet, this will be filled in as time goes on. : Cell A1 will have a "Yes" or "No" answer, if the defined range has a test score of greater then or equal to 70. : I am attempting to use the following formula in cell A1: : =If(B25:M25>=70,"Yes","No"), unfortunately this formula does not always work correctly. I think it is because Excel is interpreting the "dates" as general numbers and is throwing off the formula and providing a "Yes" answer in cell A1 when it should be "No". As an array-formula it will not do the job the way you want it. The condition part will produce an array of logical values. the formula will then give a result that will depend on the first logical value. That is in your case always Yes because B25 is a date which is represented internally as a serial number. That serial number makes the condition become true (it's a 5-digit number).

COUNTIF ITSELF DOESN'T DO THAT. BUT COMBINED WITH IF CERTAINLY.

SUPPOSING THAT YOUR SCORES NOW OCCUPY B2:B12, YOU CAN USE

=IF(COUNTIF(B2:B12,">=70")>=1,"Yes","No")