Solved how to determine if a number is on the +/- side of a number

August 1, 2017 at 06:30:55
Specs: Windows 10
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.

See More: how to determine if a number is on the +/- side of a number

Reply ↓  Report •

#1
August 1, 2017 at 06:57:43
✔ Best Answer
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


Reply ↓  Report •

#2
August 1, 2017 at 07:27:34
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


Reply ↓  Report •

#3
August 1, 2017 at 07:32:46
sorry. i meant 1.1555" guess i fat fingered the numbers. thank you for your response, i will give it a try.

Reply ↓  Report •

Related Solutions

#4
August 1, 2017 at 07:43:04
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.


Reply ↓  Report •

#5
August 1, 2017 at 08:05:01
PERFECT!!!! THANK YOU!!! EXACTLY WHAT I NEEDED!!

Reply ↓  Report •

#6
August 1, 2017 at 08:14:11

Reply ↓  Report •

#7
August 1, 2017 at 08:18:28
My bad. I'm used to doing everything in all caps. :) much appreciated for your help. good day. :)

Reply ↓  Report •

Ask Question