mardi 10 juillet 2018

Excel- Need to ignore "±" and sometimes a "°" in a larger function

I have a series of spreadsheets that list design drawing dimensions. A1 is a number corresponding to a drawing, B1 is the dimension on the drawing, C1 is the actual dimension we encountered, D1 is the manufacturing error tolerance which is something like ±.005 or ± 1°. They vary between a few numbers on each drawing, and the drawings vary. E1 is a column that needs to say either Accept or Reject, reject is when the B1 dimension is more than the D1 away from the C1 value.

I inherited the spreadsheet with the function =IF(AND(C1<=(B1+.005), C1>=(B1-.005)), "Accept", "Reject"). This works, but I need to keep changing the .005 to values like .020 and 1° and I want to finish this faster than doing it all by hand. I would like to be able to drag in down the whole E column and have it fill out accept and reject accordingly.

I tried changing it (C1<=(B1+D1)) so it pulls from the tolerance, but get a value error because every tolerance has a ± in front of it. I tried adding MID to it, but I keep getting errors because I'm not quite sure how to incorporate it.

I couldn't find other help online because most people using MID are trying to extract characters, but I need the tolerances to stay the same in the D column. Avoiding errors from "°" would also be helpful, but that's only about 5% of the data and I deal with doing those manually if I have to. Kinda new to excel, still trying to really understand the intricacies and necessary syntax of the functions, so sorry if this is and easy one.

For the Mid I tried C1<=(B1+(MID(D1,2,4))) Which I know is very wrong. Thanks for any and all help!

Aucun commentaire:

Enregistrer un commentaire