Multi-Conditional Average

**Isacael** · 03-10-2009, 07:55 AM

Hi All

Please see attached example for reference - I am trying to calculate an average if it meets 2 conditions, i.e, calulate the average for the Lead Time column if it is a Bag and On Time. Hope you guys can come through as always!

Cheers
Paul

**NBVC** · 03-10-2009, 08:04 AM

Try:

=AVERAGE(IF(A5:A28="Bag",IF(D5:D28="On Time",C5:C28)))

which must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear around the formula.

Note:

In you example you have an extra space after each occurance of Bag... please delete that space to get proper results.

**Isacael** · 03-10-2009, 08:11 AM

Hi NBVC

Thanks for the quick reply. I have added this formula, also using Ctrl+Shift+Enter but get #DIV/0!. I have added example. Would be most grateful if you could have a look.

Cheers
Paul

**NBVC** · 03-10-2009, 08:16 AM

As I mentioned in the note in my previous post... you must go and delete the extra space after the word Bag in each cell of column A.

or..

change formula to (which is not the better way):

=AVERAGE(IF(TRIM(A5:A28)="Bag",IF(D5:D28="On Time",C5:C28)))