Excel 2010 Help with Column Summary by Status Type

[lauriebizz]

I have a simple spread sheet that lists reqirement number (D) and a testing status (C:C) (No Run, Passed, Completed). I need a fomula that returns the Requirement Number with the status of the lowest status. I created the string below to assign a numeric value to the status.

=IF(C119="No Run",2,IF(C119="Passed",0,IF(C119="Failed",4,IF(C119="Not Completed",1,IF(C119="Blocked",3)))))

I've created a pivot table against the data to return the Max of the numeric value, but it doesn't work as anticipated.

Any suggestions?
Thanks!
Laurie

 

[zelgar]

Can't you use filters and sort on col C? I'm not certain I understand what you're trying to get.

 

[lauriebizz]

The confusing part is the requirement ID could have multiple regression test plans. If the regression test plan a for xyz has passed, then it needs to indicate passed. However if test plan b for xyz has failed, then it needs to indicate failed.

I'm some what confused myself, as I'm helping out a friend.

 

[SkipVought]

How about posting an example of your table, the criteria and the results you would expect from that table example.

 

[zelgar]

Since your pivot table is showing the Maximum value, I'd change your formula in Col C to change the value for a Passed result to be 5 instead of 0. Based upon your current formula, you'd never see a Passed value because it's lower than all of the other possibilities.

=IF(C119="No Run",2,IF(C119="Passed",5,IF(C119="Failed",4,IF(C119="Not Completed",1,IF(C119="Blocked",3)))))