how to compute percentage of time looking at corresponding values in columns

Dear all,

I am new to excel and have a doubt in a particular computation. I would highly appreciate your help in this regard.

1. Imagine 2 columns; A(having time displayed hh:mm:ss:ms format) & B (a value corresponding to time).
2. Assume only 2 possible values for column B e.g. Val1 or Val2
3. I would like to compute the percentage of time for which Val1 was in use & the same for Val2.

Any ideas how this could be done? Please suggest me some possible ways of getting this done.

Thanking you in advance,
Andy13

Added an excel sample to show the example. I hope this clears the issue little better.

It would be best if you uploaded a sample workbook with some data. It's not clear what you mean by " & B (a value corresponding to time)". If possible, manually show the expected result you need

You'll need an array function (CTRL, SHIFT and ENTER) such as this one:

=MAX(IF(B2:B14="Val2",A2:A14))-MIN(IF(B2:B14="Val2",A2:A14))+1

to give the actual time at Val 2

However, this won't work on your data, as excel doesn't recognise the time format you're are using. It will need to be converted to a format excel can read as a number.

If as per your sample you have a record per ms then Val2 time equates to:

=COUNTIF(B:B,"Val2")*"0:0:0.001"
format to s.000

If you just want the percentage you can ignore the Time conversion and just use:

=COUNTIF(B:B,"Val2")/(COUNTA(A:A)-COUNTA(A1))
format to %