Harold, I am impressed. I did not make my question clear but you still came up with the correct solution. The last ABS worked. Thank you.
Walter On Fri, Apr 17, 2009 at 10:26 AM, Harold Fuchs < [email protected]> wrote: > 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] >
