Average with a twist

[swaink]

Hi All

I have attempted to solve this problem myself and have found some formulas here that nearly do the trick but not quite.

In Column A I have cells that contain four characters ie BG01, BG02 etc
In column B I have time values.

Using advanced filter I have listed the unique values of those in column A I now wish to calculate the average time based on those characters.

IE give me the average time for all the times where column A contains BG01

I also need to ignore time values where the time is 00:00:00.

Any assistance that can be offered would be greatly appreciated

best regards

Kevin

 

[NBVC]

Try:

=AVERAGE(IF(A2:A100="BG01",IF(B2:B100>0,B2:B100)))

adjust ranges to suit.

Confirm with Ctrl+Shift +Enter not just Enter.

 

[swaink]

Hi There

absolutely spot on, works great

Appreciate your time and help

All the best

Kevin