Need a little help with a formual problem

J

Jesse

Hello everyone,

I have a very simple formula that is giving the wrong answer. I need
a little help as to why it is giving the wrong answer.

AN16 = =(AJ16*AK16)+(AM16*AL17) answer given $1295.99

AJ16 = 80
AK16 = $12.69

AM16 = =AK16*1.5 with an answer of $19.04
AL17 = an array
=SUM(IF(RIGHT(B17:AC17,2)="ot",IF(ISNUMBER(--MID(B17:AC17,LEN(B17:AC17)-2,1)),--LEFT(B17:AC17,LEN(B17:AC17)-2))))
with an answer of 14.75

The answer should be $1,296.04, .07 off

Can someone tell me why?

Thanks!

To email remove the underscore
(e-mail address removed)
 
J

Joe User

Jesse said:
AN16 = =(AJ16*AK16)+(AM16*AL17) answer given $1295.99
AJ16 = 80
AK16 = $12.69
AM16 = =AK16*1.5 with an answer of $19.04
AL17 = [...] with an answer of 14.75
The answer should be $1,296.04, .07 off
Click to expand...

First, 1296.04 is not ".07 off" from 1295.99.

As I understand it, AN16 becomes 80*12.69 + 12.69*1.5*14.75. That evaluates
to about 1295.97. That is indeed 0.07 "off".

Second and perhaps more pertinent, AN16 is not exactly 1295.97. It is
actually 1295.96625.

The same is true for AM16: it is not exactly 19.04. It is actually 19.035.
And that seems to be the primary source of your erroneous expectations.

Working with numbers with decimal fractions is almost never exact; and in
this case, the extra decimal digits are to be expected from the operations.

When you expect dollar-and-cents results, usually it is prudent to
explicitly round the result.

AM16 should probably be =ROUND(AK16*1.5,2).

Moreover, AN16 should probably be =ROUND(AJ16*AK16 + AM16*AL17,2)

Perhaps less obvious, AL17 should probably be the array formula:

=ROUND(SUM(IF(RIGHT(B17:AC17,2)="ot",
IF(ISNUMBER(--MID(B17:AC17,LEN(B17:AC17)-2,1)),
--LEFT(B17:AC17,LEN(B17:AC17)-2)))),2)

That is prudent to minimize "floating-point aberrations" -- infinitesimal
computation "errors" that arise because of the way that Excel stores numbers
and does arithmetic. For example, IF(10.1 - 10 = 0.1, TRUE) is surprisingly
FALSE. IF(ROUND(10.1 - 10, 1) = 0.1, TRUE) is TRUE.

PS: An alternative to explicit rounding is to set the Precision As
Displayed option under Tools > Options > Calculation in Excel 2003.
Generally, I deprecate the use of PAD because it pervasive and
indiscriminate. There may be times when you do not want to round "dollar"
amounts; for example, I do not round intermediate interest and outstanding
balance amounts in amortization schedules. In any case, if you choose to
experiement with PAD, be sure to make a back-up copy of your Excel file
first. PAD might make irreversible changes to constants.


----- original message -----
 
B

Bernard Liengme

I suspect the answer 1295.99 is correct; look at this copied from a
worksheet
AJ AK AL AM AN
16 80.00 12.69 19.04 1295.99
17 14.75Now
if we take write the formula =80*12.69+19.04*14.75 we get 1296.04 which is
0.05 different
That is the problem you have given us.

But AM16 is not really 19.04 it is =12.69*1.5 = 19.035
So =80*12.69+19.035*14.75 = 1295.966
I suspect some other values are round offs. Maybe 14.75 is not exactly that
but 14.751
AJ AK AL AM AN
16 80.000 12.690 19.035 1295.985
17 14.751Select Aj16:An17 and format to show more decimals (3 not 2) and your
apparent incorrect result is explained
best wishes
 
Top