Wednesday, 23 January 2013

Excel Function to Find First and Latest Occurrence of an Action Date Value based on Condition

How to Find First and Latest Occurrence of an Action Date Value based on Condition
Suppose we have a data of  Students and their actions like Reading , Writing , Drawing by date as follows :


If you wants to find the First  and Latest occurrence dates of a student " Will Smith" Action 'Writing' , you can find in the following way.

First Occurrence :
 {=MIN(IF(($A$2:$A$20="Will Smith")*($B$2:$B$20="Writing")*($C$2:$C$20),($C$2:$C$20)))}

Latest Occurrence : 
Method - I :
 {=MAX(IF(($A$2:$A$20="Will Smith")*($B$2:$B$20="Writing")*($C$2:$C$20),($C$2:$C$20)))}

Method - II :
{=INDEX(C1:C20,MATCH(MAX(IF((A1:A20="John")*(B1:B20="Drawing"),(C1:C20),)),C1:C20,0),1)}




In the same we can Find the Maximum of Occurrence date of any of Two actions of Particular student , using the below formula :

{=INDEX(C1:C20,MATCH(MAX(IF((($A$1:$A$20="Will Smith")*OR($B$1:$B$20="Reading",$B$1:$B$20="Writing")),($C$1:$C$20),)),$C$1:$C$20,0),1)}


Thanks,
TAMATAM

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts