Percent above

[tapsmiled]

I need to do a calculation based upon times. I will have a random number of cells, and each will have a time (duration) based upon a simple calculation derived from 2-3 adjoining cells. Let's say that this duration will be listed starting at G2 and going down. I need to calculate what percentage of the times that are a duration of less than 90 seconds...ideally striving for a percentage above 90%

Thanks!

[tapsmiled]

Here is a brief example sheet.

[benishiryo]

do note that because time is recognized as decimals in Excel, it's better to round them. so in F2, use:
=MROUND(C2-A2,"0:0:1")
i'm rounding to the nearest second. copy down

this helps you get how many are below 90 seconds and above 0
=COUNTIFS(F2:F22,"<"&TIME(,1,30),F2:F22,">0")

this helps you count how many of column A are filled:
=COUNT(A2:A22)

so just divide them & format to percentage:
=COUNTIFS(F2:F22,"<"&TIME(,1,30),F2:F22,">0")/COUNT(A2:A22)

or without relying on column F, try:
=SUMPRODUCT((MROUND(C2:C22-A2:A22,"0:0:1")<TIME(,1,30))*(MROUND(C2:C22-A2:A22,"0:0:1")>0))/COUNT(A2:A22)

[tapsmiled]

Thanks a lot. I will not be able to check it for a few days (3day weekend!!!!!), but I will take care of it when I return to work. Thank you!!

[tapsmiled]

I just applied it to my worksheet, and it seems to work perfectly. Thank you very much for the help!!

[tapsmiled]

Delete message