Hello All,

I am writing an Excel program for my employer and we are in the fabrication industry and use AutoCAD and Excel in tandem. What I am wanting to do is to write a formula that will determine if a number is either on the + side of a multiple of 1/16" (.0625) or on the - side of it. For instance, if a calculated value comes back as 1.0945" I want it to read 1.125"- if the calculated number is 1.555" I want it to read as 1.125"+. The deciding factor would be which side of the 1/32" between two 1/16ths the calculated number fell between. I hope this makes sense and that someone out there can be of some help. Thank you in advance.

I'm confused... The rounding of 1.0945 to 1.125- makes sense, but the rounding of 1.555 to 1.125+ doesn't.

1.555 is much more than 1/16th higher than 1.125. Are you sure you don't want 1.5625- for 1.555?

If so, try this:

~~=IF(A1<ROUND(A1*16,0)/16,ROUND(A1*16,0)/16&"""-",ROUND(A1*16,0)/16&"""+")~~Sorry, I forgot to deal with situations where the calculated value lands right on a 1/16th increment. Try this:

=IF(A1=ROUND(A1*16,0)/16,ROUND(A1*16,0)/16&"""",

IF(A1<ROUND(A1*16,0)/16,ROUND(A1*16,0)/16&"""-",

ROUND(A1*16,0)/16&"""+"))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Another option is to put your ROUND formula in one cell and then reference that cell for your final result. That way you always have the "rounded to 1/16" value as an actual number in case you need to use it someplace else. e.g.

With your value in A1, try this:

B1: =ROUND(A1*16,0)/16

C1: =IF(A1=B1,B1&"""",IF(A1<B1,B1&"""-",B1&"""+"))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

sorry. i meant 1.1555" guess i fat fingered the numbers. thank you for your response, i will give it a try.

Make sure you try the edited version which handles values that land exactly on a 1/16 mark.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

PERFECT!!!! THANK YOU!!! EXACTLY WHAT I NEEDED!!

No need to shout. Glad I could help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

My bad. I'm used to doing everything in all caps. :) much appreciated for your help. good day. :)

Ask Your Question

Weekly Poll

Do you think Apple's new iPhone X series of phones are groundbreaking?

Discuss in The Lounge

Poll History