Conditional Formula - How to Ignore Blank Cells

lkmw

Board Regular
Joined
Jul 1, 2005
Messages
53
I have a datasheet that contains customer dimensions and a column for the min and max allowed. Inspectors then enter their measurements which can be anywhere from 1 to 7 datapoints per dimension (quantity required varies by customer). I want to have a final column that compares each of the datapoints entered to the min and max values and indicates "OK" if they all are in tolerance, or "NOT OK" if any one or more is below min or above max. I am avoiding the macro route just because I am a novice and others here would not be able to interpret or fix either. So, I started testing a forula which at this point just has conditions for datapoints below the min. It works fine if I have data in all 7 cells. But, if I have less than 7 datapoints, it returns "NOT OK" due to the blank cells. How can I get it to ignore blanks?

Thanks in advance for the help.
0297_LW.xls
BCDEFGHIJKL
3SPECIFICATIONMINIMUMMAXIMUMSUPPLIER MEASUREMENT RESULTSRESULTS
4.100 +/- .0050.0950.1050.1000.1010.1020.0950.10150.0960.097OK
5.095/.1050.0950.1050.100NOT OK
Sheet1
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Try:

Code:
=IF(OR(MIN(E4:K4)<C4,MAX(E4:K4)>D4),"Not Ok","Ok")
copied down
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Code:
=IF(OR(MIN(E4:K4)<C4,MAX(E4:K4)>D4),"Not ","") & "OK"

<sup>edit</sup> DANG IT! Too slow! On the other hand, a very nice solution, NBVC :LOL: <sub>/edit</sub>
 

lkmw

Board Regular
Joined
Jul 1, 2005
Messages
53

ADVERTISEMENT

Ok...I have NBVC's formula in my spreadsheet and it works fine. Except, now my issue is that I created a form that is an entire page long since the number of criteria we check varies from part to part. So, I copied the formula to all lines of the spreadsheet. Let's say there were only two lines of data for a particular part like my original post. I know get "OK" in lines 3 through the bottom of the sheet even though there is no data. How do I still handle blanks per my previous post, but have the "Results" cell stay blank if all 7 of the 'supplier measurement results' cells in a particlar row are blank?
0297_LW.xls
ABCDEFGHIJKL
6ITEMSPECIFICATIONMIN.MAX.SUPPLIERMEASUREMENTRESULTSRESULTS
7A.100+/-.0050.0950.1050.1000.1010.1020.0950.10150.0960.097Ok
8B.095/.1050.0950.1050.1000.095Ok
9Ok
10Ok
11Ok
12Ok
13Ok
14Ok
15Ok
16Ok
Sheet1


Thanks
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Yes, we could give you the answer, but then you won't learn anything... :biggrin: What do you think you need to do? Hint: It will involve the <s>IsBlank()</s> Sum() function.
 

lkmw

Board Regular
Joined
Jul 1, 2005
Messages
53

ADVERTISEMENT

I give up....searched through Excel's help on Sum function but still do not get it.
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
How about?

=IF(SUM(E7:K7)=0,"",IF(OR(MIN(E7:K7)<C7,MAX(E7:K7)>D7),"Not Ok","Ok"))

or


=IF(COUNTBLANK(E7:K7)=7,"",IF(OR(MIN(E7:K7)<C7,MAX(E7:K7)>D7),"Not Ok","Ok"))
 

lkmw

Board Regular
Joined
Jul 1, 2005
Messages
53
Thank you, NBVC. I used the IF(SUM) part of the latest formula you suggested with the latter part of your original formula so that I get min and max and it seems to work fine....even when I go back and delete results entries from a row using various methods. A number of folks will be using the spreadsheet so it's always fun seeing what 'creative' ways they come up with for doing things.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,323
Messages
5,600,954
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top