Identify a range based on adjacent column....

[Alex_Sal]

Hello,

I have a column of times people used a door (per day), and a column saying who it was each time, I want a way to take the greatest of these values and subtract the lowest of these values for each person, to find the total time in between. So I used this formula :

Max(range)-Min(range)

The problem is that these ranges vary in size, so I can't just choose the ranges myself because there are too many people. I want a way to identify all times associated to one name, and then take the max - the min, in other words, find the range based on an output column with each unique name.

here's what i have to work with:
xl example.png

Thanks for your help.


edit: you can ignore column I that was my failed earlier attempt.

[josch]

Hi,
i have a similar problem and found following solution :

MAX(INDEX((Sheet2!C3:C997=C1)*D3:D997;0))

In words : Find the Max of those entries (Range D3-D997), where the cellvalue in the C column = C1.

What Idont know - what the "*" operator is - in other words, why it works. Anybody around here know this ?
Update 1: Sample File selectionsample.xls

Update 2: "Why does it work" - it seems to be a matrix/array operation. With my office, I can just enter it. Others post, that you need to leave the "celleditor" with STRG-SHIFT-ENTER to enable this.

Update 3: Details for the "*" operator can be found here - "*" is the AND-Operator for array-operations. Those operators are needed because following is illegal

=MIN(IF(AND(A1:A10=”value1″,B1:B10=”value2″),C1:C10))

Josh

[Alex_Sal]

Hi, thanks for the reply.

I thought the * just means multiply? In any case this formula doesn't work for me it says there's an error, i think because there is no row reference given to the index function. I have tried various alterations but nothing seems to help.

I'm sure this should be simple...

[:) Sixthsense :)]

Array formula requires CTRL+SHIFT+ENTER

Please Login or Register  to view this content.

adjust the formula to your range

[Alex_Sal]

thanks this is very interesting, what do you mean adjust the formula for my range - change just the ranges - or something else as well? As it stands it's still not finding the right results.

To clarify my problem, if you look at the example i loaded earlier you'l see the value 8:09 in ColJ adjacent to my own name (Ignore ColI), this is the correct number (16:59 - 08:49), but it was found by me manually selecting the range for the max-min value. I need a way for excel to figure out that range, based on the values in ColH drawing from the information in A and B.

All ideas appreciated, in the meantime I'm going to play around with If's and Or's

[josch]

Hi,
here is a selectionsample.xls samplesheet for the formular. Works just fine for me.

Josh