2009/4/17 Walter Hildebrandt <[email protected]> > Putting *=(A1-B1)/B1* in C1 sometimes gives the wrong answer in C1 when > positive and negative numbers are involved. > For these example cells A1 and B2 are formatted to dollars and C1 is > formatted to percent. > > When A1 is $10 and B1 is $5 C1 is 100% which is correct > When A1 is -$10 and B1 is $5 C1 is -300% which is correct
Correct up to now. > > When A1 is $10 and B1 is -$5 C1 is -300% which is not correct. 300% would > be the correct in C1 Sorry but your arithmetic teacher needs to be taught arithmetic. If A1 is 10 and B1 is -5 then (A1-B1) is 15. Divide that by -5 and the answer is -3 (-300%) as per Calc. > > When A1 is -$10 and B1 is -$5 C1 is 100% which is not correct -100% would > be correct in C1 Again, your arithmetic teacher need lessons. If A1 is -10 and B1 is -5 then (A1-B1) is -5. Divide that by -5 gives 1 (100%) > > What formula can be used in C1 to get the correct answer > Calc is giving the arithmetically correct answers. Your version of arithmetic is clearly different from that of the rest of the world. If you can *define* what you mean by "correct" then perhaps ... If you *always* want positive nunbers then I recommend you look up the ABS function in the help. Your formula might then become ABS(A1-B1)/ABS(B1). Another version, which on reflection might be closer to what you want would be (A1-B1)/ABS(B1). But, as I say, Calc *is* doing what you told it to do. -- Harold Fuchs London, England Please reply *only* to [email protected]
