# Average with a twist

#### swaink

##### Active Member
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

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Try:

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

Confirm with Ctrl+Shift +Enter not just Enter.

Hi There

absolutely spot on, works great

All the best

Kevin

Replies
0
Views
222
Replies
5
Views
304
Replies
1
Views
371
Replies
2
Views
185
Replies
5
Views
221

1,219,914
Messages
6,150,937
Members
450,995
Latest member
LBlke

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back