If Statement issue

[leeb-1977]

Hi folks - this has been doing my head in all day.... can anyone offer any help?!

I have a range of cells (F10 to K10) that have numeric values, they are measurements basically. I have another set of cells X10 and Y10, cell X10 contains the minimum allowed measurment result after tolerance has been applied... while Y10 contains the Maximum allowed result after tolerance.

In cell L10 i want to review the six results in cells F10 - K10 and if cell falls out of the allowed range set in X10 and Y10 then i want cell L10 to display NO.... if all results are fine then i want cell L10 to display YES.

I've been trying to find a formula that allows this to happen all day and im finally admitting defeat and asking for help!

The closet i got was:

=IF(OR(F10,G10,H10,I10,J10,K10<X10>Y10),"No","Yes")

but all the above does is return a value of NO regards of what my measurement data says.....


Can anyone offer any advice before I lose my mind??

Thanks guys

Lee.

 

[Airfix9]

Will repost

 

[Airfix9]

=IF(OR(MIN(F10:K10)<X10,MAX(F10:K10)>Y10),"No","Yes")

 

[leeb-1977]

Why oh Why didnt i just post here earlier!! thankyou - youve stopped me staring at the screen loooong into the night

 

[Airfix9]

You're welcome. We are all guilty of occasionally over-engineering a problem. Often, from the plethora of possible answers that are available in Excel, we overlook the simplest! I know I do.

 

[leeb-1977]

I cant thankyou enough.... however could i ask if theres a way to modify that slightly....

Ive enetered the formula into Excell and it works perfectly, however on my form there are 50 rows. I've copied the formula so that its over all 50.... but on the ones that dont have any data it returns a NO.... is there any way to disregard empty cells so i dont get a NO value each time?

I know i could just delete the formula where its not needed but i want to issue out my form to people and lock as many cells as possible.

Thanks again

Lee.

 

[Relysis]

Does this work?

=IF(COUNTBLANK(F10:K10),"INCOMPLETE",IF(OR(MIN(F10:K10)<X10,MAX(F10:K10)>Y10),"No","Yes"))

 

[Airfix9]

=IF(COUNTBLANK(F10:K10),"INCOMPLETE",IF(OR(MIN(F10:K10)<X10,MAX(F10:K10)>Y10),"No","Yes"))