compair test value against range-conditionally

[Buzzed]

I inherited a database that is used to create certificates of conformance. the basic layout is as follows:

lot | product | date of manufacture | specific measurement | standard dev | test range

or:
Lot1233 | acme holes | 7/12/1975 | 3.14 | .43 | 3-3.3

I am looking for a conditional format that would fill the specific measure if the value was out of spec.

This is easy enough with version 2007. However, what do you when the specs are to be determined (TBD) or simply not performed on that particular product (NA)?

Also, specifications may be one sided, that is greater than a certain number, say 7, or smaller than, say 21.

so the possible specs will include: a range (3.1-3.2), a min only (>3), a max only (<21), not determined (TBD), or not tested (NA).

I would like to test for TBD and NA conditions and if not present then compair the measurement against the spec.

I am not sure how to do this as Excel thinks the measurement is a number and the min/max are text values separated by a dash "-".

Any ideas?

 

[Damon Ostrander]

Hi Buzzed,

The conditions you mention are too complicated to handle directly with conditional formatting. What I would do would be to add (at least) three extra columns: a minimum value, a maximum value, and and in spec/out of spec column. The min and max value column values would contain formulas that would parse the min and max values from your test range column, testing for TBD, NA, -, >, and < to provide an appropriate value. The in/out of spec column would contain TRUE/FALSE depending on whether the measurement meets the criteria in the the min and max columns. Again, its formula would have to handle TBD, NA, and the possible absence of a max or min value.

Finally, you could then use the in/out of spec column TRUE/FALSE value as the basis for the conditional formatting. The extra columns could be hidden if so desired.

I hope this helps.

Damon

 

[Buzzed]

This is the approach that I was hoping to avoid. I currently have over 1500 datapoints.

I know what to do from here.

Thanks for the insight.

 

[Damon Ostrander]

Hi again Buzzed,

Is your concern with the 1500 data points that the three extra columns will add 4500 more data values, and increase the size of the file? 1500 points doesn't sound very large to me, and from what you have told us about your database I would guess the whole file is currently less than 1 MB.

But if this is a problem, there is another way. You could use a user-defined function (UDF) to do the in/out-of-spec determination, and use that in the conditional formatting. This would not require the addition of the three columns, and would add very little to the size of the workbook. Would you like more information on this?

Damon

 

[Buzzed]

Damon, thanks for the UDF idea. I'll give this some thought. I'm not at a programmer so I find VBA to be a bit intimidating. The file size is not a concern. 1500 datapoints is a lot for me the problem is that we run more than 1 test. It's more like 7 so I would be adding something like 21 helper columns. With this much going on, I worry about database management.

Perhaps the best thing to do would be to move the database over to Access.

 

[Damon Ostrander]

Hi again Buzzed,

Using a UDF is quite easy. As soon as the code is copied into a code module in your workbook it becomes available just as if it were a built-in worksheet function.

But you are right--Excel is not the way to implement any but the simplest databases. If you expect to have to use this database for quite a while, and especially if you expect it to grow, converting it to Access sooner rather than later would be a good idea.

Keep Excelling.

Damon

 

[Buzzed]

Thanks again Damon,

Now I just have to convince the my IT person and the guy that fills in when I am out of the office that MS Access is the way to go :-0