Decimal place confusion

[abesimpson]

I import various numeric values into an Excel spreadsheet in simplest terms they look like this:

A1 B1 C1
12.25 12.2552 =12.2*12.25

What I am looking for is a formula that will take the value in A1 (to 1 decimal place) and multiply it by the value in cell B1 (to 2 decimal places) such that the answer is 149.45. I do not want to use the ROUND function.

Many thanks in advance.

abe

 

[repairman615]

Hello,

Try:

<TABLE style="WIDTH: 279pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=372><COLGROUP><COL style="WIDTH: 279pt; mso-width-source: userset; mso-width-alt: 6802" width=372><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #eaf1dd; WIDTH: 279pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=372>=LEFT(A1,FIND(".",A1,1)+1)*LEFT(A1,FIND(".",A1,1)+2)</TD></TR></TBODY></TABLE>

 

[Rick Rothstein]

I do not want to use the ROUND function.

I'm curious as to why given that the ROUND function exists to pretty much do exactly what you asked for.

 

[mikerickson]

I think that you are looking for the Trunc function
=TRUNC(A1, 1)*TRUNC(A2, 2) will do what you want.

By the way, what you want to do will mess you up badly. Doing arithmetic on rounded or truncated number causes inaccuracy. It is far more accurate to do all the calculation to as much accuracy as possible and round/truncate just before printing.

(If anyone points out that it the sum of the last digits in a report doesn't match is off; they are announcing to the whole room that they don't have much experience with numbers and that their opinion isn't worth much.)

 

[Rick Rothstein]

I think that you are looking for the Trunc function

Quite right! I missed the "5" in the .25 to .2 "rounding" which is why I answered the way I did in my post (otherwise I might have thought to suggest TRUNC as well).

By way of explanation, I would have edited my original message instead of responding here, but my "edit time limit" had already expired.

 

[abesimpson]

I'm curious as to why given that the ROUND function exists to pretty much do exactly what you asked for.

I don't believe the ROUND function does what I need, for example, if rounded to 1 decimal place 12.25 will = 12.3

By the way, what you want to do will mess you up badly. Doing arithmetic on rounded or truncated number causes inaccuracy. It is far more accurate to do all the calculation to as much accuracy as possible and round/truncate just before printing.

I realize that I will be introducing a mathematical error to the calculation but what I am doing is meeting the standard of my industry for my calculation.

Thanks for the help and cautions.

abe

 

[Rick Rothstein]

I don't believe the ROUND function does what I need, for example, if rounded to 1 decimal place 12.25 will = 12.3

Yes, I noted that after I read mikerickson's message and I poste a note about it against his message.

 

[abesimpson]

Right, mea culpa and again, thanks 10^6

abe