Sorry I have already asked this but the answer has been lost somewhere, or i
can't find it now.

I have a list of data which i want to match the words then calculate the
difference between the first and second of the list, then sum all the
differences, i.e

bill 10
ben 9
fred 8
june 7
bill 6
ben 5
bill 4
ben 3
june 2

so i want it to return bill 6 (10-6 + 6-4), ben 6, june 5.

Any ideas on how to match the words, search for the next match then find the
differnce and keep that going for a list of 200 items with 20 different
names, summing all the differences?

Look at your post here, click on your name, this should open up a box. Then
click on Recent Posts by this user. That will bring up your other posts.
--
John C


"LiAD" wrote:

Sorry I have already asked this but the answer has been lost somewhere, or i
can't find it now.

I have a list of data which i want to match the words then calculate the
difference between the first and second of the list, then sum all the
differences, i.e

bill 10
ben 9
fred 8
june 7
bill 6
ben 5
bill 4
ben 3
june 2

so i want it to return bill 6 (10-6 + 6-4), ben 6, june 5.

Any ideas on how to match the words, search for the next match then find the
differnce and keep that going for a list of 200 items with 20 different
names, summing all the differences?

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

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

In your original post you gave the following example of input and
output

<qoute

bill 10
ben 9
fred 8
june 7
bill 6
ben 5
bill 4
ben 3
june 2

so i want it to return bill 6 (10-6 + 6-4), ben 6, june 5.

<end quote

My interpretation of this was that the parenthesis was just an
information to us how the result 6 for bill was obtained from the
input, not that "10-6 + 6-4" should be part of the output.
If the "10-6 + 6-4" should be part of the output, why did you not
state "9-3" for the ben output and "7-2" for the june output??

If my interpretation is correct, i.e. the stuff within the parenthesis
is just an explanation and not part of the output, then all
intermediate values for a person, the value 6 for bill in your
example, will net out to zero as it will be included in two
differences, and the final result will be the first value minus the
last value. If that is not what you want, maybe the example is could
be better choosen.

Now you say that you want "a list of all the differences..."
Where do you want that list? In one or several cells?

Could you please state exactly how you want the information to look
like for the given example above, or for any better example.
In which cells are the input?
In which cell(s) do you expect the output?

Lars-Åke


On Wed, 6 Aug 2008 03:33:08 -0700, LiAD
wrote:

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

Ok heres the proper stuff

Column L the text list I want to match - Bill, Ben, Fred (187 options in
total)
In column G against each line of column L i have a list of dates and times
in the format of 15/8/2008 15:00
In column O i need the formula to return a number of hours, that is the
subtraction of the first (a) time and the next (a+1) time value such as the
following;

Col L Col G Col O (time to
repeat of name)
Bill 15/8/2008 15:00 116 (time in
this row minus time in next bill row)
Ben 15/8/2008 17:00 124
Fred 20/8/2008 11:00 2
Bill 20/8/2008 11:00
Fred 20/8/2008 13:00
Ben 20/8/2008 19:00

and so it continues. So the function needs to

1 - look at the text string in the first row
2 - look down the list to find a match
3 - calculate the difference between the two times and return it adjacent to
the first occurance of the name
4 - continue down the entire list looking for matches and returning the time
differences

Does this help

"Lars-Ã…ke Aspelin" wrote:

In your original post you gave the following example of input and
output

<qoute

bill 10
ben 9
fred 8
june 7
bill 6
ben 5
bill 4
ben 3
june 2

so i want it to return bill 6 (10-6 + 6-4), ben 6, june 5.

<end quote

My interpretation of this was that the parenthesis was just an
information to us how the result 6 for bill was obtained from the
input, not that "10-6 + 6-4" should be part of the output.
If the "10-6 + 6-4" should be part of the output, why did you not
state "9-3" for the ben output and "7-2" for the june output??

If my interpretation is correct, i.e. the stuff within the parenthesis
is just an explanation and not part of the output, then all
intermediate values for a person, the value 6 for bill in your
example, will net out to zero as it will be included in two
differences, and the final result will be the first value minus the
last value. If that is not what you want, maybe the example is could
be better choosen.

Now you say that you want "a list of all the differences..."
Where do you want that list? In one or several cells?

Could you please state exactly how you want the information to look
like for the given example above, or for any better example.
In which cells are the input?
In which cell(s) do you expect the output?

Lars-Ã…ke


On Wed, 6 Aug 2008 03:33:08 -0700, LiAD
wrote:

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

On Tue, 5 Aug 2008 06:44:01 -0700, LiAD
wrote:

Sorry I have already asked this but the answer has been lost somewhere, or i
can't find it now.

I have a list of data which i want to match the words then calculate the
difference between the first and second of the list, then sum all the
differences, i.e

bill 10
ben 9
fred 8
june 7
bill 6
ben 5
bill 4
ben 3
june 2

so i want it to return bill 6 (10-6 + 6-4), ben 6, june 5.

Any ideas on how to match the words, search for the next match then find the
differnce and keep that going for a list of 200 items with 20 different
names, summing all the differences?

As I understand your description of the problem, it would be the same
to say that you are looking for the difference of the two values
corresponding to the first and the last occurence of a given name.
If that is correct you may try the following formula.

Assuming that your names are in the range A1:A200 and the numbers in
the range B1:B200, put the following in cell D1:

=INDEX(B$1:B$200,1000-MAX(((A$1:A$200)=C1)*(1000-ROW(A$1:A$200))))-INDEX(B$1:B$200,MAX((A$1:A$200=C1)*ROW(A$1:A$200)) )

Copy this formula down as far as needed and write your names, bill,
ben, june, etc in cells C1, C2, C3 etc.

Cells D1, D2, D3 etc will show the differences for the respective name

Hope this helps / Lars-Åke

On Tue, 05 Aug 2008 14:10:25 GMT, Lars-Åke Aspelin
wrote:

On Tue, 5 Aug 2008 06:44:01 -0700, LiAD
wrote:

Sorry I have already asked this but the answer has been lost somewhere, or i
can't find it now.

I have a list of data which i want to match the words then calculate the
difference between the first and second of the list, then sum all the
differences, i.e

bill 10
ben 9
fred 8
june 7
bill 6
ben 5
bill 4
ben 3
june 2

so i want it to return bill 6 (10-6 + 6-4), ben 6, june 5.

Any ideas on how to match the words, search for the next match then find the
differnce and keep that going for a list of 200 items with 20 different
names, summing all the differences?

As I understand your description of the problem, it would be the same
to say that you are looking for the difference of the two values
corresponding to the first and the last occurence of a given name.
If that is correct you may try the following formula.

Assuming that your names are in the range A1:A200 and the numbers in
the range B1:B200, put the following in cell D1:

=INDEX(B$1:B$200,1000-MAX(((A$1:A$200)=C1)*(1000-ROW(A$1:A$200))))-INDEX(B$1:B$200,MAX((A$1:A$200=C1)*ROW(A$1:A$200)) )

Copy this formula down as far as needed and write your names, bill,
ben, june, etc in cells C1, C2, C3 etc.

Cells D1, D2, D3 etc will show the differences for the respective name

Hope this helps / Lars-Åke


Forgot to mention that the formula is an array formula that has to be
entered with CTRL+SHIFT+ENTER rather than just ENTER.

Lars-Åke

sorry i get a list of zero's for all of the names.

Maybe it will be easier if it was simplified into just making a list of all
the differences for each person rather than summing them all up. The rest of
the operations i can do after.

thanks

"Lars-Ã…ke Aspelin" wrote:

On Tue, 05 Aug 2008 14:10:25 GMT, Lars-Ã…ke Aspelin
wrote:

On Tue, 5 Aug 2008 06:44:01 -0700, LiAD
wrote:

Sorry I have already asked this but the answer has been lost somewhere, or i
can't find it now.

I have a list of data which i want to match the words then calculate the
difference between the first and second of the list, then sum all the
differences, i.e

bill 10
ben 9
fred 8
june 7
bill 6
ben 5
bill 4
ben 3
june 2

so i want it to return bill 6 (10-6 + 6-4), ben 6, june 5.

Any ideas on how to match the words, search for the next match then find the
differnce and keep that going for a list of 200 items with 20 different
names, summing all the differences?

As I understand your description of the problem, it would be the same
to say that you are looking for the difference of the two values
corresponding to the first and the last occurence of a given name.
If that is correct you may try the following formula.

Assuming that your names are in the range A1:A200 and the numbers in
the range B1:B200, put the following in cell D1:

=INDEX(B$1:B$200,1000-MAX(((A$1:A$200)=C1)*(1000-ROW(A$1:A$200))))-INDEX(B$1:B$200,MAX((A$1:A$200=C1)*ROW(A$1:A$200)) )

Copy this formula down as far as needed and write your names, bill,
ben, june, etc in cells C1, C2, C3 etc.

Cells D1, D2, D3 etc will show the differences for the respective name

Hope this helps / Lars-Ã…ke


Forgot to mention that the formula is an array formula that has to be
entered with CTRL+SHIFT+ENTER rather than just ENTER.

Lars-Ã…ke

You will get zero's in the D column if you don't have anything in the
corresponding cell in the C column.
Please make sure that you have written bill in C1, ben in C2, june in
C3 and so on.

Lars-Åke

On Tue, 5 Aug 2008 23:57:01 -0700, LiAD
wrote:

sorry i get a list of zero's for all of the names.

Maybe it will be easier if it was simplified into just making a list of all
the differences for each person rather than summing them all up. The rest of
the operations i can do after.

thanks

"Lars-Åke Aspelin" wrote:

On Tue, 05 Aug 2008 14:10:25 GMT, Lars-Åke Aspelin
wrote:

On Tue, 5 Aug 2008 06:44:01 -0700, LiAD
wrote:

Sorry I have already asked this but the answer has been lost somewhere, or i
can't find it now.

I have a list of data which i want to match the words then calculate the
difference between the first and second of the list, then sum all the
differences, i.e

bill 10
ben 9
fred 8
june 7
bill 6
ben 5
bill 4
ben 3
june 2

so i want it to return bill 6 (10-6 + 6-4), ben 6, june 5.

Any ideas on how to match the words, search for the next match then find the
differnce and keep that going for a list of 200 items with 20 different
names, summing all the differences?

As I understand your description of the problem, it would be the same
to say that you are looking for the difference of the two values
corresponding to the first and the last occurence of a given name.
If that is correct you may try the following formula.

Assuming that your names are in the range A1:A200 and the numbers in
the range B1:B200, put the following in cell D1:

=INDEX(B$1:B$200,1000-MAX(((A$1:A$200)=C1)*(1000-ROW(A$1:A$200))))-INDEX(B$1:B$200,MAX((A$1:A$200=C1)*ROW(A$1:A$200)) )

Copy this formula down as far as needed and write your names, bill,
ben, june, etc in cells C1, C2, C3 etc.

Cells D1, D2, D3 etc will show the differences for the respective name

Hope this helps / Lars-Åke


Forgot to mention that the formula is an array formula that has to be
entered with CTRL+SHIFT+ENTER rather than just ENTER.

Lars-Åke

Tried but doesn't work for some reason, sorry.

Any other ideas of how to get a find a word string and calculate the
difference between two values associated with these words?

"LiAD" wrote:

Sorry I have already asked this but the answer has been lost somewhere, or i
can't find it now.

I have a list of data which i want to match the words then calculate the
difference between the first and second of the list, then sum all the
differences, i.e

bill 10
ben 9
fred 8
june 7
bill 6
ben 5
bill 4
ben 3
june 2

so i want it to return bill 6 (10-6 + 6-4), ben 6, june 5.

Any ideas on how to match the words, search for the next match then find the
differnce and keep that going for a list of 200 items with 20 different
names, summing all the differences?

"doesn't work" is not very helpful.
What result did you get? What problems did you encounter?

On Tue, 5 Aug 2008 11:21:00 -0700, LiAD
wrote:

Tried but doesn't work for some reason, sorry.

Any other ideas of how to get a find a word string and calculate the
difference between two values associated with these words?

"LiAD" wrote:

Sorry I have already asked this but the answer has been lost somewhere, or i
can't find it now.

I have a list of data which i want to match the words then calculate the
difference between the first and second of the list, then sum all the
differences, i.e

bill 10
ben 9
fred 8
june 7
bill 6
ben 5
bill 4
ben 3
june 2

so i want it to return bill 6 (10-6 + 6-4), ben 6, june 5.

Any ideas on how to match the words, search for the next match then find the
differnce and keep that going for a list of 200 items with 20 different
names, summing all the differences?