evaluating columns of data

[BJ]

Here's my problem: I am comparing two columns of data to
calculate the percent change of each data item
(before/after analysis). This I do with a simple delta
percent equation. Then, I need to know what is the
greatest shift, the smallest shift and the average. At
the bottom of the column containing the results of the
delta calculations, I use the =min(xx:xx), =max(xx:xx) and
=average(xx:xx) functions, where xx:xx represents a cell
range. If all the calculation results were positive
numbers, this would be sufficient. Occasionally, the
result of the calculation is a negative number. In this
case, the minimum value returned would be the LOWEST
actual number from the column, not necessarily the
smallest delta percent. Likewise, the LARGEST delta
percent may actually be the lowest number, if it is a
negative number.

I can partially solve this problem by setting up an
additional column that returns the absolute values for
these calculations and get my min, max and average values
there. However, I also need to know whether the shift was
in a positive or a negative direction, and calculating
from absolute values does not give me that information.

The only way I have been able to solve both problems is to
generate the absolute column, perform my calculations, and
then go back to the original delta percent data and
manually determine whether the number is positive or
negative, which is a huge waste of time and defeats the
whole purpose. With large amounts of data, there is also
the increased potential for mistakes.

There must be a more efficient way of doing this. Anyone
know the answer?

 

[Ken Wright]

If I read this right, and you want to do away with all your other columns other
than the two data columns:-

then assuming your data is in Cols A & B, with B being subtracted from A (Change
ranges and calc order to suit):-

In any single cell

=INDEX((A1:A100-B1:B100),MATCH(MAX(ABS(A1:A100-B1:B100)),ABS(A1:A100-B1:B100),0)
)
array entered using CTRL+SHIFT+ENTER

will give you the greatest shift as well as denoting whether it is positive or
negative


=INDEX((A1:A100-B1:B100),MATCH(MIN(ABS(A1:A100-B1:B100)),ABS(A1:A100-B1:B100),0)
)
array entered using CTRL+SHIFT+ENTER

will give you the smallest shift as well as denoting whether it is positive or
negative


Not sure what you mean by the Average though. Do you want the average of the
absoluet greatest and absolute smallest, or the average of all the real shifts
with negatives actually counting as negative etc?

 

[BJ]

Thanks for the quick response Ken! I'll give it a try.

 

[Ken Wright]

By the way, you didn't say what you wanted if you had a shift of +10 and -10.
The formulae will only give you one of them, and it will be the first one it
finds. The magnitude of the shift will be the same in either case, but the sign
won't, but you gave no indication on how you handle that scenario..