I am making a design table in excel. For modeling purposes, I have the exact lengths of my material in one column i.e. 1.125, 1.375, 1.50, 1.625, 1.75, 1.875, 2.00 etc. Then I am using a formula to put those exact values into a part description, however the description must be rounded as follows: 1.12, 1.38, 1.50, 1.62, 1.75, 1.88, 2.00 etc. How do I do this in excel for an entire column? (notice how some round up and some round down depending in the odd or even number before the 5)
You're rounding towards the even number - .375 goes up to .38, but .625 goes down to .62. Pretty conventional; it's called "Banker's Rounding".
The short answer to your question is, "Excel doesn't offer Banker's Rounding".
This link to a Microsoft knowledgebase article:
https://support.microsoft.com/en-us/kb/196652
Shows how to create a banker's rounding function. Search on the word BROUND to find the function and its calls.
Is it worth the effort? Can you simply use the ROUND function?
In excel use the formula =ROUNDUP(cell number,no of decimal places)
ie =ROUNDUP(1.22,1) will round to 1.3
the value 1.22 can also be a cell reference
Stephen
and likewise for rounding down use =ROUNDDOWN(1.22,1) will round down to 1.2
Try this, might need tweaking:
=if((left(right(A1,2),1),2)/2=0,roundup(A1,2),rounddown(A1,2))
left and right query the second to last number, if determines if it is even or odd and then applies the correct rounding. This may not work if there are trailing zeros.
This may also work:
for a fixed rounding precision:
if(integer(A1*1000)/2=0,roundup(A1,2),rounddown(A1,2))
one or a combination of these will work with the correct tweaking depending on your dataset (assuming it is much larger and diverse than your example)
Had some errors in mine, this tested and works:
=IF(MOD(LEFT(RIGHT(A1*1000,2),1),2)=0,ROUNDDOWN(A1,2),ROUNDUP(A1,2))
I had not seen these replies until just now. In the mean time another employee of ours came up with this equation:
=TEXT((ROUND(((EVEN((TRUNC(A1/2,3))*1000))*0.002),2)), "0.00")
The text function was used because we needed to use the final value in a CONCATENATE function and that only reads text (the column itself could not be changed to text in this instance). The text was also kept to 0.00 places, which our part description also needed to be kept at.
Thank you very much for your other responses!
I had not seen these replies until just now. In the mean time another employee of ours came up with this equation:
=TEXT((ROUND(((EVEN((TRUNC(A1/2,3))*1000))*0.002),2)), "0.00")
The text function was used because we needed to use the final value in a CONCATENATE function and that only reads text (the column itself could not be changed to text in this instance). The text was also kept to 0.00 places, which our part description also needed to be kept at.
Thank you very much for your other responses!