The Fort Wayne Chapter of the IIAFORT WAYNE. IN - 2018-04-18

Featured Product

Excel Daily News

If Function

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)&LT;&GT;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).

(6 responses) I'm part of an organisation that is altering the way it manages a waiting list, and we have decided we want a way of picking n people per ye

Let's Connect

MrExcel.com debuted on November 21, 1998.

MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures
for illustration only, without warranty either expressed or implied, including
but not limited to the implied warranties of merchantability and/or fitness for
a particular purpose. The Formulas, Functions and Visual Basic procedures on this
web site are provided "as is" and we do not guarantee that they can be used in all
situations.
This site contains affiliate links. Any affiliate commissions that we
earn when you click a link to Amazon or other sites is reinvested in keeping MrExcel.com
running. You can earn a commission for sales leads that you send to us by joining our
affiliate program.
View my Privacy Policy and Terms of Use.