Hi, I am in need of a formula which can distinguish between whole numbers and decimals in a column and from there subtract/add a value based on the number. E.g number = 80 then +/- 2.5, number = 80.0 then +/- 0.8, number = 80.00 then +/- 0.38
Any help would be great and I can try my best to explain/clear anything up.
Welcome to the Forum Jonor55!
To tell if a number is a whole number:
=IF(MOD(A1,1)=0,"Whole","Decimal")
However, the example you gave doesn't make sense because all three examples are whole numbers, and they are all the same whole number:
80 = 80.0 = 80.00
In Excel, the only difference between those three numbers is how they are displayed. They are all stored as the same number.
So it's completely unclear what you are trying to illustrate in your example. Also, one either adds or subtracts but not both at the same time. How do you decide which one you want to do? And how do you determine the value to add or subtract?
It might help to explain the larger context--why do you need to do this?
Forgot to put the the post below as reply to you.
Sorry, I used 80 as an example, what i mean is X, X.X, X.XX, It is more to do with the number of decimal places in the rather than the numerical value. I was just going to use two different columns with the same script but change the minus to a plus.
The larger context:
I have a dimension X, then based on its value I will get a min / max tolerance, the tolerances vary depending whether it is X, X.X or X.XX
Capture.JPG
80 at A3:A5
Please try at B3 drag to C3 and drag down
=$A3+LOOKUP(IFERROR(LEN($A3)-FIND(".",$A3),0),{0,1,2},{2.5,0.8,0.38})*2*((COLUMNS($B3:B3))-1.5)
I tried the formula you gave Bo and it works! Thanks so much!
FIND(".",$A3),0) will always give a #VALUE error when applied to a whole number, even if it is displayed with a decimal point and trailing zeroes as shown in the example. Similarly LEN will return 2 all the values shown in the first column.
Jonor55, please attach an actual Excel file so we can test solutions without re-creating your file from scratch. The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks