Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
mtpsuresh
 
Posts: n/a
Default Formula behaves differently


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.


--
mtpsuresh
------------------------------------------------------------------------
mtpsuresh's Profile: http://www.excelforum.com/member.php...o&userid=31817
View this thread: http://www.excelforum.com/showthread...hreadid=543761

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default Formula behaves differently

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)

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default Formula behaves differently

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.

"mtpsuresh" wrote
in message ...

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.


--
mtpsuresh
------------------------------------------------------------------------
mtpsuresh's Profile:
http://www.excelforum.com/member.php...o&userid=31817
View this thread: http://www.excelforum.com/showthread...hreadid=543761



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jerry W. Lewis
 
Posts: n/a
Default Formula behaves differently

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/micro...06871cf92f8465
will allow you to more closely examine the approximations involved, if you
so desire.

Jerry

"mtpsuresh" wrote:


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.


--
mtpsuresh
------------------------------------------------------------------------
mtpsuresh's Profile: http://www.excelforum.com/member.php...o&userid=31817
View this thread: http://www.excelforum.com/showthread...hreadid=543761


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
mtpsuresh
 
Posts: n/a
Default Formula behaves differently


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


--
mtpsuresh
------------------------------------------------------------------------
mtpsuresh's Profile: http://www.excelforum.com/member.php...o&userid=31817
View this thread: http://www.excelforum.com/showthread...hreadid=543761

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match then lookup Tenacity Excel Worksheet Functions 9 December 3rd 05 06:30 AM
Formula Problem - interrupted by #VALUE! in other cells!? Ted Excel Worksheet Functions 17 November 25th 05 06:18 PM
adding row to forumla carrera Excel Discussion (Misc queries) 9 August 23rd 05 10:24 PM
Creating a check mark box MarthaSue Setting up and Configuration of Excel 18 April 28th 05 12:31 AM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 05:37 PM


All times are GMT +1. The time now is 11:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"