just tried this sorry but it gets well tangled and completely bottoms out
when i try to ask it to trace the error. the cell says n/a.

i'm lost

"Bernie Deitrick" wrote:

MIN works differently than MAX in array formulas, unless you are working with negative numbers,
because of all the 0 values returned.

Again, CTRL+SHIFT+ENTER rather than just ENTER.
=IF(COUNTIF(L$1:L1,L1)=1,MIN(IF(L$1:L$187=L1,O$1:O $187)),"")

HTH,
Bernie
MS Excel MVP


"LiAD" wrote in message
...
cheers works well.

sorry been away on hols so not been on it. is it possible you could explain
to me how the last part of this formula works please? why the O&O1 and the
last bracketed parts?

i have tried to get this to do the same thing but for the minimums in the
next column across from the macimums but i can't get it. sorry for the
stupid questions.

Thanks


"Lars-Åke Aspelin" wrote:

Here is one formula that you may try in cell Q1 and copy down to cells
Q2 to Q187.

Note: This is an array formula that should be entered with
CTRL+SHIFT+ENTER rather than just ENTER.

=IF(COUNTIF(L$1:L1,L1)=1,MAX((0&O$1:O$187)*((L$1:L $187=L1))),"")

This will put the max value for each person on the same row as the
first occurance of that name in column L.

Hope this helps / Lars-Åke


On Thu, 7 Aug 2008 05:12:01 -0700, LiAD
wrote:

works perfect. Thanks very much for your help.

Another related question - whats the best approach if I want to then find the
max value in the list in column O for each person? i.e. excel looks at all
values for bill and return the max to column Q?

I promise thats the last

"Lars-Åke Aspelin" wrote:

The 124 in your example should be 122 I think.

Assuming your data starts on row 1 and that the information in column
G is actually formatted time values rather than text, try the
following in cell O1:

=IF(MOD(COUNTIF(L$1:L1,L1),2),24*(INDEX(G2:G$187,M ATCH(L1,L2:L$187,0))-G1),"")

Copy down to cells O2 to O187
Make sure to change the 187 if your data table increases.

This will give the time difference between the second and first
occurance of Bill on the same row as the first occurance of Bill,
the time difference between the forth and third occurance of Bill on
the same row as the third occurance of Bill and so on.
Column = will have blanks on the same rows as the second, fourth, and
so on, occurance of any name.

Hope this helps / Lars-Åke


On Thu, 7 Aug 2008 01:39:12 -0700, LiAD
wrote:

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