Ok, I guess I am oversimplifying this and making assumptions that are invalid, but it appeared to me that the original question was how to determine in a sequence if a sample pair in sequence represents an increase or decrease in value. If that is the case then the ABS() function represents the magnitude of the change and all that is necessary is to determine the sign of the change. This can be demonstrated with:
Cell B2 -6 -3 55 100 50 0.3
Cell B3 -3 -6 11 50 100 0.75
Results +3 -3 -44 -50 +50 +0.45
Using formula in results cell: =IF(B2>B3,-1,1)*ABS(B2-B3)
This works for all signed numbers. The magnitude of the change is simply multiplied by a +/- 1 depending on the direction of the change. I don't see how this result would change depending on version of spreadsheet or other calculator.
If you wanted the results for unsigned inputs only then the order of subtraction is important.
Emeritus Assoc in Pathology
From: Campus Computer Coordinators [mailto:[log in to unmask]] On Behalf Of Todd L. Sherman / KB4MHH
Sent: Monday, November 15, 2010 1:38 PM
To: [log in to unmask]
Subject: Re: Excel problem...
I have 2003. Think maybe it's a difference in the way 2003 handles things
compared to more recent versions?
One person gave me a formula and all it did was show the correct NUMBERS but
wouldn't show positive or negative. Other people suggested the simple
formula and it wasn't working in mine. It would show negatives, but it was
making positives a negative and vice-versa.
Someone just recently sent me an attachment with the "simple" formula and it
finally worked, but I noticed that it took a good 15 seconds to "convert",
When someone gave me the more COMPLICATED formula earlier, it took care of
It's as simple as that. Right now, though, I kinda feel like evveryone's
out to call me a liar, or tell me that I don't know what I'm talking about,
and that I'm imagining everything I'm experiencing. (sigh)
Maybe 2003 requires more complication in it's math to get the same job
> -----Original Message-----
> From: Cain M Norris [mailto:[log in to unmask]]
> Sent: Monday, November 15, 2010 12:02 PM
> To: [log in to unmask]
> Subject: Re: Excel problem...
> So, I've checked that Excel behaves correctly here, and it
> does. See attached demo.
> The formula "=+IF(+F8>F9,-ABS(F8-F9),ABS(F9-F8))" is exactly
> equivalent to "=F9-F8". You can convince yourself by inspection.
> If F8 > F9, then the difference F8-F9 must be positive, so
> the absolute value in "-ABS(F8-F9)" has no effect, and the
> first clause of the IF is equivalent to "F9-F8", by
> distributing the "-".
> Similarly, if F8 <= F9 (ie, in the second clause of the IF)
> then the difference F9-F8 must be non-negative, so again, the
> absolute value has no effect, and the second clause is
> equivalent to "F9-F8".
> Now, like I said, I confirmed that the two formulas do behave
> equivalently in Excel, but there's one other bugaboo I'll
> mention: some of Excel's formatting options treat
> arithmetical and logical formulas a little differently (for
> reasons that don't make much sense). If you're running into
> that problem, for instance with inconsistent value
> formatting, it might look like the formulas themselves were to blame.