Formula needed for calculation

[rshetty_79]

I am looking for a formula with which I want to calculate the "Unique scripts executed" out of a chunk of data.


Details:

A script is executed if the 'status' column says Passed or Failed.

A script is identified by its name shown in column 'Plan: Test Name'

Unique scripts are one which are taken in the count only once even if they are executed more than once.


Logic:

1. Unique scripts = The latest 'Exec Date' where a particular 'Plan: Test Name' appears more than once and the 'Status' is Passed or Failed.

2. Count of the Column 'Exec Date' where the Status is Passed or Failed and 'Exec Date' is <Today's date>

3. if a script has been executed more than once on the same day then the column 'Time' should be taken for checking the latest time.


Exec Data, Plan: Test name, Status, Time are all Column titles.


Exec Date = Date

Plan Test Name = script name

Status = Passed, Failed, Blocked, N/A.

Time = Time stamp in following format 4:31:43 PM


I wanted to attach the excel to this question, but there is not option to do so to this.

 

[Luke M]

How to share data:

http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[rshetty_79]

hi Luke,


Below is the link

https://docs.google.com/open?id=0B3C0UEqCBRa1QlJXbVlXVFQ3WlU

 

[Faseeh]

Hi rshettym,


Can you explain the process taking up an example? What is the formula supposed to do?


Regards,

 

[Faseeh]

@ Excel Ninjas: Re-Posted by mistake, plz discard/delete. Thanks

 

[rshetty_79]

The formula is supposed to give me the count of the Column 'Exec Date' where the status column has valued 'Passed' or 'Failed ' and 'Exec Date' value should be 'Today's date'


Also there has to be one more loop to the above the formula. The column 'Plan Test Name' may have a value which may me repeated more than once for the same day (i.e. today). In such a case, it should consider the value in the column 'Time' for such repeated 'Plan test name' and ONLY the latest timestamp one should be considered for count.


Best Regards,

 

[rshetty_79]

Example:


As per the table, the count of unique scripts executed should be 30.


If I apply filter, on the Exec date for "today's date" and on the Status for 'Passed' and "failed". I get around 32 rows. So the count should be 32 for unique scripts executed.

BUT.. there is 1 script in the column 'Plan: Test Name' (TC_1_P_1126) which has been executed more than once on the same day. So, I Manually checked the value in the column 'Time' for 3 repetitions. And as per that the latest script (TC_1_P_1126) was the one which had the Time stamp - 4:31:43 PM.


So, I subracted 2 from the count of 32 and arrived at the count of 30 as Unique scripts executed.


I hope this example helps.

 

[Faseeh]

Hi rshetty,


I have added a helper column with this formula, enter in Col H and drag down:

=IF(OR(B2="PASSED",B2="FAILED"),1,0)


Now enter this formula in some other cell:


=SUMPRODUCT((C2:C42=TODAY())*(H2:H42))-SUMPRODUCT((ISBLANK($D$2:$D$42)=FALSE)*1)+1

This will work fine if you have only One such case of duplicate instances for which you subtract 2, for more then that it will need adjustment. Just let me know if that is the second case.


Regards,