Ok, complicated setup this time, but I need to describe exactly what I'm
doing so I don't end up asking the wrong thing:

I have three named columns:

A1-A450. ObjectName - Contains the name of an object
B1-B450. FirstOwner - Contains the name of the first person who bought a
specific object
C1-C450. SecondOwner - Contains the name of the second owner, ie the next
person who bought the object. If the object has only been sold once, this
column is blank (no object has ever been sold three times).

Now I need to find out the name of the most frequent owner for a specific
object. Finding out the most frequent FirstOwner is easy - I've done it as
follows:

{=IFERROR(INDEX(FirstOwner;MEDIAN(IF(FirstOwner<" ";IF(ObjectName="Shovel";MATCH(FirstOwner;FirstOwn er;0)))));"None")}

This formula checks if the Object is a Shovel, then checks what the median
value is of all FirstOwners of Shovel and returns the content of the median
value, thanks to INDEX/MATCH.

The problem is, I need to find out the most frequent AND most recent owner
of an object, meaning that if there is only one owner I want to look in
FirstOwner, but if there is a second owner, I want the formula to pick from
the column Second Owner. I tried to solve this by adding an extra column,
named CurrentOwner, which only displayed the most recent owner, by simply
having it read =IF(C1="";B1;C1) - you see how this would list the seond owner
if there was one, and the first if the object had only been sold once.

The problem is, the array formula I used above doesn't work at all if I
replace FirstOwner with CurrentOwner. It's as though the array formula breaks
completely when there is a formula in the named range. I've checked the Name
Manager and the values for CurrentOwner look perfectly fine - still, I can't
get it to work in my main formula.

Any help on A) getting my CurrentOwner named range into my array formula, or
B) working around the problem in an elegant way. will be very appreciated. In
the meantime, I'll try working on a clumsy solution with several IF
statements.

Thanks for your time.

I had no trouble getting your neat formula to work with CurrentOwner.
What did you mean by "didn't work"? Wrong results? Error value?
Email me at my private email (remove capital letters) and I will send you my
file.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Babymech" wrote in message
...
Ok, complicated setup this time, but I need to describe exactly what I'm
doing so I don't end up asking the wrong thing:

I have three named columns:

A1-A450. ObjectName - Contains the name of an object
B1-B450. FirstOwner - Contains the name of the first person who bought a
specific object
C1-C450. SecondOwner - Contains the name of the second owner, ie the next
person who bought the object. If the object has only been sold once, this
column is blank (no object has ever been sold three times).

Now I need to find out the name of the most frequent owner for a specific
object. Finding out the most frequent FirstOwner is easy - I've done it as
follows:

{=IFERROR(INDEX(FirstOwner;MEDIAN(IF(FirstOwner<" ";IF(ObjectName="Shovel";MATCH(FirstOwner;FirstOwn er;0)))));"None")}

This formula checks if the Object is a Shovel, then checks what the median
value is of all FirstOwners of Shovel and returns the content of the
median
value, thanks to INDEX/MATCH.

The problem is, I need to find out the most frequent AND most recent owner
of an object, meaning that if there is only one owner I want to look in
FirstOwner, but if there is a second owner, I want the formula to pick
from
the column Second Owner. I tried to solve this by adding an extra column,
named CurrentOwner, which only displayed the most recent owner, by simply
having it read =IF(C1="";B1;C1) - you see how this would list the seond
owner
if there was one, and the first if the object had only been sold once.

The problem is, the array formula I used above doesn't work at all if I
replace FirstOwner with CurrentOwner. It's as though the array formula
breaks
completely when there is a formula in the named range. I've checked the
Name
Manager and the values for CurrentOwner look perfectly fine - still, I
can't
get it to work in my main formula.

Any help on A) getting my CurrentOwner named range into my array formula,
or
B) working around the problem in an elegant way. will be very appreciated.
In
the meantime, I'll try working on a clumsy solution with several IF
statements.

Thanks for your time.

I also used a Pivot Table to find what was required.
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Babymech" wrote in message
...
Ok, complicated setup this time, but I need to describe exactly what I'm
doing so I don't end up asking the wrong thing:

I have three named columns:

A1-A450. ObjectName - Contains the name of an object
B1-B450. FirstOwner - Contains the name of the first person who bought a
specific object
C1-C450. SecondOwner - Contains the name of the second owner, ie the next
person who bought the object. If the object has only been sold once, this
column is blank (no object has ever been sold three times).

Now I need to find out the name of the most frequent owner for a specific
object. Finding out the most frequent FirstOwner is easy - I've done it as
follows:

{=IFERROR(INDEX(FirstOwner;MEDIAN(IF(FirstOwner<" ";IF(ObjectName="Shovel";MATCH(FirstOwner;FirstOwn er;0)))));"None")}

This formula checks if the Object is a Shovel, then checks what the median
value is of all FirstOwners of Shovel and returns the content of the
median
value, thanks to INDEX/MATCH.

The problem is, I need to find out the most frequent AND most recent owner
of an object, meaning that if there is only one owner I want to look in
FirstOwner, but if there is a second owner, I want the formula to pick
from
the column Second Owner. I tried to solve this by adding an extra column,
named CurrentOwner, which only displayed the most recent owner, by simply
having it read =IF(C1="";B1;C1) - you see how this would list the seond
owner
if there was one, and the first if the object had only been sold once.

The problem is, the array formula I used above doesn't work at all if I
replace FirstOwner with CurrentOwner. It's as though the array formula
breaks
completely when there is a formula in the named range. I've checked the
Name
Manager and the values for CurrentOwner look perfectly fine - still, I
can't
get it to work in my main formula.

Any help on A) getting my CurrentOwner named range into my array formula,
or
B) working around the problem in an elegant way. will be very appreciated.
In
the meantime, I'll try working on a clumsy solution with several IF
statements.

Thanks for your time.