Hi all,
I badly need your help in the below formula

Example:
Structure : Account includes Sub-accounts and each sub-account includes dials

Formula : Account activation date = Oldest Sub-account activation date
where there is more than one account and hence different activation
date for each account and in turn the sub-accounts that belong to each account

Below the example

A B C D
Account # Sub-Account # Account Sub-account
activation activation
date date

2 2.11 ?? 21-01-2010
2 2.12 ?? 26-01-2010
2 2.13 ?? 03-02-2010
3 3.111 ?? 14-02-2010
3 3.112 ?? 29-04-2010

Account Activation date for account#2 shall be 21-01-2010 (Oldest
sub-account activation date)

Many thanks,
Khaled

Hi,

Try this ARRAY formula. Put it in C2, ARRAY enter it (see below) and drag down

=MIN(IF($A$2:$A$6=A2,$D$2:$D$6))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"khaled shaheen" wrote:

Hi all,
I badly need your help in the below formula

Example:
Structure : Account includes Sub-accounts and each sub-account includes dials

Formula : Account activation date = Oldest Sub-account activation date
where there is more than one account and hence different activation
date for each account and in turn the sub-accounts that belong to each account

Below the example

A B C D
Account # Sub-Account # Account Sub-account
activation activation
date date

2 2.11 ?? 21-01-2010
2 2.12 ?? 26-01-2010
2 2.13 ?? 03-02-2010
3 3.111 ?? 14-02-2010
3 3.112 ?? 29-04-2010

Account Activation date for account#2 shall be 21-01-2010 (Oldest
sub-account activation date)

Many thanks,
Khaled

Hi Mike,

Thanks for your fast response. It's returning the same oldest date
(21-01-2010)for the 2 account numbers (1 & 2) although the oldest date for
each of them is different

Please help

"Mike H" wrote:

Hi,

Try this ARRAY formula. Put it in C2, ARRAY enter it (see below) and drag down

=MIN(IF($A$2:$A$6=A2,$D$2:$D$6))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"khaled shaheen" wrote:

Hi all,
I badly need your help in the below formula

Example:
Structure : Account includes Sub-accounts and each sub-account includes dials

Formula : Account activation date = Oldest Sub-account activation date
where there is more than one account and hence different activation
date for each account and in turn the sub-accounts that belong to each account

Below the example

A B C D
Account # Sub-Account # Account Sub-account
activation activation
date date

2 2.11 ?? 21-01-2010
2 2.12 ?? 26-01-2010
2 2.13 ?? 03-02-2010
3 3.111 ?? 14-02-2010
3 3.112 ?? 29-04-2010

Account Activation date for account#2 shall be 21-01-2010 (Oldest
sub-account activation date)

Many thanks,
Khaled

Mike H's formula does gives the correct result, if you follow Mike's
instructions to press CTRL+Shift+Enter. (Select the formula cell,
click F2 and press CTRL+Shift+Enter, and drag down the formula)

Regards,
Per







On 16 Maj, 22:17, khaled shaheen
wrote:
Hi Mike,

Thanks for your fast response. It's returning the same oldest date
(21-01-2010)for the 2 account numbers (1 & 2) * although the oldest date for
each of them is different

Please help



"Mike H" wrote:
Hi,

Try this ARRAY formula. Put it in C2, ARRAY enter it (see below) and drag down

=MIN(IF($A$2:$A$6=A2,$D$2:$D$6))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

"khaled shaheen" wrote:

Hi all,
I badly need your help in the below formula

Example:
Structure : Account includes Sub-accounts and each sub-account includes dials

Formula : Account activation date = Oldest Sub-account activation date
*where there is *more than one account and hence different activation
date for each account and in turn the sub-accounts that belong to each account

Below the example

* * A * * * * * * * * *B * * * * * * * * * * C * * * * * * *D
Account # * *Sub-Account # * *Account * *Sub-account *
* * * * * * * * * * * * * * * * * * * * *activation * * * activation
* * * * * * * * * * * * * * * * * * * * * * date * * * * * * * date * * * * *

*2 * * * * * * * * *2.11 * * * * * * * * * ?? * * * * * 21-01-2010
*2 * * * * * * * * *2.12 * * * * * * * * * ?? * * * * * 26-01-2010
*2 * * * * * * * * *2.13 * * * * * * * * * ?? * * * * * 03-02-2010
*3 * * * * * * * * *3.111 * * * * * * * * ?? * * * * * *14-02-2010
*3 * * * * * * * * *3.112 * * * * * * * * ?? * * * * * *29-04-2010

Account Activation date for account#2 shall be 21-01-2010 (Oldest
sub-account activation date)

Many thanks,
*Khaled- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -