| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding, kinda. :)
Unique situation. Customer does not want our values "rounded" to 3 places.
They want them "truncated" to 3 places, without regard to the 4th decimal place. (Note: I know truncate is the incorrect function. But that's what they said. I know what they really want.) To clarify . . . One column pulls measured data from our data base as . . . 1.2508794 1.2504556 1.2499987 1.2500124 etc. They want to "see" & use in calcs . . . 1.250 1.250 1.249 1.250 Rounding to 3 places would work for 2 of the above entries, but not 1.2508794 or 1.2499987. They would round to 1.251 and 1.250. I need to do this for 3 columns, then do some minor calcs using the 3 columns. So the values I use must also be the 3 decimal "un-rounded" values. Yeah, seems strange. This study is checking some values, assuming the accuracy of a machine to only be 3 places. They don't want the 4th place to effect the 3rd decimal place. So I have 3 columns of data now. (one set of columns for one dimension) Nominal, actual reading and upper tolerance. I need to perform a function to get each column formatted as listed above. And do it for a couple hundred "sets" in this study. The Nom and Tolerance column is no issue. But reformatting the actual data? 1). I can't figure out how to just use 3 decimal places without rounding. This is what I "should" end up with . . . Nominal Upper Tolerance Actual New Actual 1.250 .010 1.2508794 1.250 1.250 .010 1.2504556 1.250 1.250 .010 1.2499987 1.249 1.250 .010 1.2500124 1.250 2). Will I need to insert an extra column (New Actual) next to each "Actual" column. Then perform the same function to the Actual column for each New Actual column. Doing this 200 times will be quite a chore. Then I have to do it for 2 other data sets, one is 60 times, one is 42 times. Any ideas would be most appreciated. I'm not asking for a hand out. But I really can't get this to work. Hmmmm. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200803/1 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rounding, kinda. :)
Scrap this - I did not know that there was more to the post than was showing
on my screen. -- Ooops! Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... =TRUNC(A1,3) and format to show three decimal places otherwide 1.250 will show as 1.25 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Wayne Knazek via OfficeKB.com" <u26683@uwe wrote in message news:8110271674b3d@uwe... Unique situation. Customer does not want our values "rounded" to 3 places. They want them "truncated" to 3 places, without regard to the 4th decimal place. (Note: I know truncate is the incorrect function. But that's what they said. I know what they really want.) To clarify . . . One column pulls measured data from our data base as . . . 1.2508794 1.2504556 1.2499987 1.2500124 etc. They want to "see" & use in calcs . . . 1.250 1.250 1.249 1.250 Rounding to 3 places would work for 2 of the above entries, but not 1.2508794 or 1.2499987. They would round to 1.251 and 1.250. I need to do this for 3 columns, then do some minor calcs using the 3 columns. So the values I use must also be the 3 decimal "un-rounded" values. Yeah, seems strange. This study is checking some values, assuming the accuracy of a machine to only be 3 places. They don't want the 4th place to effect the 3rd decimal place. So I have 3 columns of data now. (one set of columns for one dimension) Nominal, actual reading and upper tolerance. I need to perform a function to get each column formatted as listed above. And do it for a couple hundred "sets" in this study. The Nom and Tolerance column is no issue. But reformatting the actual data? 1). I can't figure out how to just use 3 decimal places without rounding. This is what I "should" end up with . . . Nominal Upper Tolerance Actual New Actual 1.250 .010 1.2508794 1.250 1.250 .010 1.2504556 1.250 1.250 .010 1.2499987 1.249 1.250 .010 1.2500124 1.250 2). Will I need to insert an extra column (New Actual) next to each "Actual" column. Then perform the same function to the Actual column for each New Actual column. Doing this 200 times will be quite a chore. Then I have to do it for 2 other data sets, one is 60 times, one is 42 times. Any ideas would be most appreciated. I'm not asking for a hand out. But I really can't get this to work. Hmmmm. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200803/1 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rounding, kinda. :)
Right, unless I misread the rest of the post, it looks like you want to
calculate the New Actual from the Actual, if that is so then my original post hold true. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... =TRUNC(A1,3) and format to show three decimal places otherwide 1.250 will show as 1.25 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Wayne Knazek via OfficeKB.com" <u26683@uwe wrote in message news:8110271674b3d@uwe... Unique situation. Customer does not want our values "rounded" to 3 places. They want them "truncated" to 3 places, without regard to the 4th decimal place. (Note: I know truncate is the incorrect function. But that's what they said. I know what they really want.) To clarify . . . One column pulls measured data from our data base as . . . 1.2508794 1.2504556 1.2499987 1.2500124 etc. They want to "see" & use in calcs . . . 1.250 1.250 1.249 1.250 Rounding to 3 places would work for 2 of the above entries, but not 1.2508794 or 1.2499987. They would round to 1.251 and 1.250. I need to do this for 3 columns, then do some minor calcs using the 3 columns. So the values I use must also be the 3 decimal "un-rounded" values. Yeah, seems strange. This study is checking some values, assuming the accuracy of a machine to only be 3 places. They don't want the 4th place to effect the 3rd decimal place. So I have 3 columns of data now. (one set of columns for one dimension) Nominal, actual reading and upper tolerance. I need to perform a function to get each column formatted as listed above. And do it for a couple hundred "sets" in this study. The Nom and Tolerance column is no issue. But reformatting the actual data? 1). I can't figure out how to just use 3 decimal places without rounding. This is what I "should" end up with . . . Nominal Upper Tolerance Actual New Actual 1.250 .010 1.2508794 1.250 1.250 .010 1.2504556 1.250 1.250 .010 1.2499987 1.249 1.250 .010 1.2500124 1.250 2). Will I need to insert an extra column (New Actual) next to each "Actual" column. Then perform the same function to the Actual column for each New Actual column. Doing this 200 times will be quite a chore. Then I have to do it for 2 other data sets, one is 60 times, one is 42 times. Any ideas would be most appreciated. I'm not asking for a hand out. But I really can't get this to work. Hmmmm. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200803/1 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rounding, kinda. :)
=TRUNC(A1,3) and format to show three decimal places otherwide 1.250 will
show as 1.25 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Wayne Knazek via OfficeKB.com" <u26683@uwe wrote in message news:8110271674b3d@uwe... Unique situation. Customer does not want our values "rounded" to 3 places. They want them "truncated" to 3 places, without regard to the 4th decimal place. (Note: I know truncate is the incorrect function. But that's what they said. I know what they really want.) To clarify . . . One column pulls measured data from our data base as . . . 1.2508794 1.2504556 1.2499987 1.2500124 etc. They want to "see" & use in calcs . . . 1.250 1.250 1.249 1.250 Rounding to 3 places would work for 2 of the above entries, but not 1.2508794 or 1.2499987. They would round to 1.251 and 1.250. I need to do this for 3 columns, then do some minor calcs using the 3 columns. So the values I use must also be the 3 decimal "un-rounded" values. Yeah, seems strange. This study is checking some values, assuming the accuracy of a machine to only be 3 places. They don't want the 4th place to effect the 3rd decimal place. So I have 3 columns of data now. (one set of columns for one dimension) Nominal, actual reading and upper tolerance. I need to perform a function to get each column formatted as listed above. And do it for a couple hundred "sets" in this study. The Nom and Tolerance column is no issue. But reformatting the actual data? 1). I can't figure out how to just use 3 decimal places without rounding. This is what I "should" end up with . . . Nominal Upper Tolerance Actual New Actual 1.250 .010 1.2508794 1.250 1.250 .010 1.2504556 1.250 1.250 .010 1.2499987 1.249 1.250 .010 1.2500124 1.250 2). Will I need to insert an extra column (New Actual) next to each "Actual" column. Then perform the same function to the Actual column for each New Actual column. Doing this 200 times will be quite a chore. Then I have to do it for 2 other data sets, one is 60 times, one is 42 times. Any ideas would be most appreciated. I'm not asking for a hand out. But I really can't get this to work. Hmmmm. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200803/1 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rounding, kinda. :)
Ya know, that's what I get for believing Microsoft! According to THEIR
definition . . . "Truncates a number to an integer by removing the fractional part of the number." Their example shows using trunc with a number like 8.99. The result was 8. That's why I added the note at the beginning of my post saying trunc was not the correct function! Good grief. Thanks guys. Anyone got an idea for the #2 question in my post? -- Message posted via http://www.officekb.com |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rounding, kinda. :)
Glad that it helped and all four of us Sandy's thank you for the feedback
<g -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Wayne Knazek via OfficeKB.com" <u26683@uwe wrote in message news:81108cf335334@uwe... Ya know, that's what I get for believing Microsoft! According to THEIR definition . . . "Truncates a number to an integer by removing the fractional part of the number." Their example shows using trunc with a number like 8.99. The result was 8. That's why I added the note at the beginning of my post saying trunc was not the correct function! Good grief. Thanks guys. Anyone got an idea for the #2 question in my post? -- Message posted via http://www.officekb.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COMMENT question . . . kinda. :) | Excel Discussion (Misc queries) | |||
| Comments box, kinda. | Excel Discussion (Misc queries) | |||
| I need to know what kinda Function to Use in A Worksheet | Excel Worksheet Functions | |||
| Rounding Up Kinda | Excel Discussion (Misc queries) | |||
| Some kinda lookup | Excel Worksheet Functions | |||
Hybrid Mode
