Rounding, kinda. :)

[Wayne Knazek via OfficeKB.com]

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.

 

[Sandy Mann]

=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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

=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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Wayne Knazek via OfficeKB.com]

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?

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk