Lookup for oldest date among different dates

  • Thread starter Thread starter khaled shaheen
  • Start date Start date
K

khaled shaheen

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.
 
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'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
 
Back
Top