Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / May 2006

Tip: Looking for answers? Try searching our database.

Formula behaves differently

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
mtpsuresh - 19 May 2006 16:49 GMT
Hi,

I have a peculiar problem. I display TRUE or FALSE in a cell and is
based on the below formula.
=(IF(ISNUMBER($B66),($AK66=($P66-$V66+$AA66+$AB66-$AF66)),""))

But the value displayed is FALSE. The value in AK66 is equal to 650.38
and the sum of ($P66-$V66+$AA66+$AB66-$AF66) is also equal to 650.38.

The same formula is copied to the entire column and it shows TRUE for
other cells and is as expected. I tried with several values.

Can anybody help me out pls.

Signature

mtpsuresh

aidan.heritage@virgin.net - 19 May 2006 17:11 GMT
Excel stores it's numbers to a 15 Decimal Place precision, so it's
probably an internal rounding problem - if you force excel to round by
using the round function, you will resolve this

=round(ak66,2)=round(etc etc etc,2)
Andy - 19 May 2006 17:21 GMT
Hi

Your formula looks a bit odd! It is saying that if B66 is a number, then
$AK66=($P66-$V66+$AA66+$AB66-$AF66).
Eh?
Are you trying to compare AK66 to $P66-$V66+$AA66+$AB66-$AF66? See if this
does what you want:
=IF(AND(ISNUMBER($B66),$AK66=$P66-$V66+$AA66+$AB66-$AF66),"Yes","No")
This is untested - and might not be what you want anyway!

Andy.

> Hi,
>
[quoted text clipped - 9 lines]
>
> Can anybody help me out pls.
Jerry W. Lewis - 19 May 2006 18:55 GMT
I know of no documented instance of basic arithmetic errors in Excel.

Excel and almost all other computer software does binary arithmetic.  Most
terminating decimal fractions (including 650.38) are non-terminating binary
fractions that can only be approximated (just as 1/3 can only be approximated
as a decimal fraction).

Likely your calculation simply resulted in a different binary approximation
to 650.38 due to the binary approximations to the initial inputs.  With
addition/subtraction, you can simply round the final result to smallest
decimal place of any of the inputs without doing violence to the calculation.

My D2D function at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465
will allow you to more closely examine the approximations involved, if you
so desire.

Jerry

> Hi,
>
[quoted text clipped - 9 lines]
>
> Can anybody help me out pls.
mtpsuresh - 22 May 2006 12:14 GMT
Thanks for your replies.

The problem was in the decimal fractions as all of you have pointed
out. I've corrected it.

Once again thanks for all your valuable time and effort for me.

Regards
Suresh

Signature

mtpsuresh

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.