Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
adding row to forumla | Excel Discussion (Misc queries) | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |