can this be done?

[Abbo]

hi all,
im looking to have a tolerance for a value in column D depending on the
entry in coloumn A (product size). if over or under this tolerance then the
entry is highlighted.
for each product size the these highlighted entrys are totaled ie if there
was three of them then it would = 3.
i would then like to have sum sort of function that compares column B values
of the highlighted entries, if they have the same column B enteries it gives
a 1 if there are two different values = 2 ect.

 

[Abbo]

is this possible? any help at all would be greatly welcome

 

[Fred Smith]

You can most likely do it with conditional formatting (under the Format menu).
However, we need more detail to give a specific answer.

If you need more help, post back with an example of the data you have, and what
result you want.

 

[Abbo]

Hi fred thanks for gettin back,
this what the sheet looks like at the mo,

Part Size Lot # Crimped Marker balloon proximal distal
Length Distance Length Shoulder Shoulder
2.00*08 6062931 8.186 8.379 9.499 0.308 -0.788
2.00*08 6062832 N/A 8.17 9.295 0.218 -0.428
2.50*12 6062931 N/A 8.362 8.6 0.141 -0.121

there are six columns basically we measure parts then put these results into
excel.
theres part size and product lot# the rest a measurements we take.

what i would like to be able to do is have a tolerance for the value in
coulnm D depending on what part size. so for 2.00*08 it is 8.0 +- 0.2 ie it
can have a value anywhere between 7.8 and 8.2.for the first entry it is
outside this tolerance, so it would be highlighted.
for each product size the amoumt of products out of tolerance would be
totaled. so for 2.00*08 this would = 1, as the other 1 is in spec,...with me
so far?

now, idealy, and i dunno if this can be done but, i would like to have some
sort of function to compare the coloumn B values of the highlighted
enteries. if the same give a value of 1 if there are two different values
give a 2 if there are three etc.
so for our example 2.00*08 there are two different lot numbers so in some
other column it would give a value of 2.
basically thats what i need, the sheet then is updated every day ie we
measure and enter about 40 parts a day into the sheet.

could i do this with conditional format? do i need multiple sheets maybe for
each product size currently there all lumped onto 1 sheet
any help would be great thanks

 

[Fred Smith]

Conditional formatting will do the first part of what you want to do. However,
you will need to specify how to decipher the Part Length in column A.
Specifically, how do you get from "2.00*08" to 8.0 +/- 0.2?

Is column A a text cell?
Is the format always going to be x.xx*yy?
Will the tolerance factor (x.xx) always be one digit before the decimal, and two
after?
Will the length (yy) always be two digits?
To get the tolerance, is the formula x.xx/10?

 

[Abbo]

very sorry fred i got mixed up,
to clarify that value(2.00*08) is two dimensions, width by length so its
2.0mm by 8.0mm.
for each product size there is a spec for the distance between the outer
ends (marker distance) which is a bit longer. so for product size 2.00*08 its
8.5mm +/- 0.2 basically these are all known spec. for the 2.50*08 it would be
8.7mm +/- 0.2mm there is no formula to it its just a given spec (that we
know) for a givin product size.
the product size is always given that way 2.00*08, 2.00*23, 2.25*12,
4.00*28
hope this clarifies
regards
Abbo

 

[Fred Smith]

Sorry, Abbo, I still need my questions answered. Most importantly, is the value
(eg, 2.00*08), stored as text, and is it always in the same format?

 

[Abbo]

its not stored as text,
it can be, and yes its always in the same format

regards
Abbo

 

[Fred Smith]

OK, if it's not stored as text, how is it stored? If you put 2.00*08 in a cell,
and it's not text, Excel will treat it as a formula, which will generate a
result of 16. How can we determine the dimensions from this result?

 

[Abbo]

hi fred,
okay i didnt know excel would think of it as a formula. so i put it in as a
text cell. i have a list of values for marker distance ie for 2.00*8 the
marker distance should be 8.5mm +/- 0.2mm for the 2.50*12 its 12.7mm +/-
0.2mm and so on for all our sizes. do i need to create a list of these and
get excel to compare the measured marker distance (ie column D) with this
list?
Regards
Abbo

 

[Fred Smith]

Well, Abbo, I'm confused.

In your first post, you said the for product 2.00*08, the tolerance was 8.0mm
+/- 0.2mm, because the tolerance was based on the part number.

Now you say it's 8.5mm +/- 0.2mm, and it's a spec, not a formula. So we'll go
with that. You will, of course, have to store the specs (or look them up) in
your file.

You say you have 6 columns (A-F) in your spreadsheet, so let's assume you put
the spec length in G and the spec tolerance in H. I'm also assuming row 1 is the
column titles, and your data starts in row 2.

In d2, you would put the following conditional format:

=ABS(D2-G2)>H2

After you enter the formula, tell Excel what format you want to appear if the
condition is true (ie, the length is outside the specs). Typical formats are a
colored pattern.

Then copy down the format for the length of the column. The easiest way to do
this is to right-click on the fill button (the little square in the bottom right
hand corner), drag it down the length of the column, release the mouse button,
and choose Fill formatting only.