Yes I want all the values in between as well not just the highest minus the
lowest. At the end I would like a list of all of all the differences between
sequential Fred and Bill etc values that I can then play with to get max's,
avs etc.

Sorry I know my descriptions aren't clear maybe - trouble is i tried to
simplify the example as my data is a lot more complicated and not possible to
write in this.

In summary I would like excel to look through the list of names find two
names that match and return the difference between the two values associated
with these names, complete that for the full list of 200 values. From this
list I can then juggle whatever way I want, for now I do not how to ask excel
to find two equal text strings, find the values associated with these text
strings and perform a calculation on these two values, returning the value to
a specified cell.

Does this help or confuse?

"John C" wrote:

I guess my question is are you really wanting the difference of the highest
value minus the lowest value? What if there are intermediary values?
In your example, you give 3 values of Fred, 67, 23, and 10, and state a
difference of 57. This is actually the high minus the low, which can be
portrayed easily in the array** formula of:
=MAX(IF(A$1:A$100=C12,B$1:B$100))-MIN(IF($A$1:$A$100=C12,$B$1:$B$100))
Where C12 is who you are looking for the difference for (such as Fred).

However, if you want the overall difference of all the numbers, then in your
example, it would be 114 for Fred, as 67-23=44, 67-10=57, 23-10=13, and
subsequently 44+57+13 = 114

--
John C


"LiAD" wrote:

I want to get excel to look through a series of numbers and words, find the
words that match, calculate the difference between the two numbers associated
with these words and then sum all of the differences from the list, e.g.

fred 10
bill 21
ben 34
bob 21
fred 23
bill 23
fred 67

I want excel to tell me the sum of differences for fred as 57, bill as 2 and
the rest all 0.

I have about 200 lines to sort with about 100 combinations of words.

Any ideas much appreciated.

Thanks