Thread: formula to get an average based on a dropdown list.

Hello People,

Im creating a spreadsheet at work, which has a drop down box with the options of open and closed in it.

I also have a column for length of time of the claim, what i need to do, is now find a way of getting a average length of a claim, but only to see the closed option from the drop down box.

for example,

row 1
open date - 02/06, closed date - 01/07, dropdown box - closed, life - 29
row 2
open date - 10/06, closed date - n/a, dropdown box - open, life - -39400

as you can see from the example above, i cant simply do a average of the whole column, as would read the open ones as well. so is there a way of doing the formula, but telling it to only see the open, or closed ones?

You help will be greatly appreciated!!

I forgot, can you use AVERAGEIF in XL2003...

=AVERAGEIF(C4:C14;"open";D4:D14)

If not try this:

=SUMPRODUCT(--($C$4:$C$14="open");$D$4:$D$14)/SUMPRODUCT(--($C$4:$C$14="open"))

try to replace ; with , due to different settings.

Try second way (also replace ; with ,) and adopt formula to where your data is placed (C4:C14 and D4:D14 is just example

=SUMPRODUCT(--($C$4:$C$14="open"),$D$4:$D$14)/SUMPRODUCT(--($C$4:$C$14="open"))

Perhaps posting a workbook sample may help resolve your issue..

As I see it there's no need to use SUMPRODUCT - use SUMIF/COUNTIF which are more efficient:

=SUMIF($C$4:$C$14,"Open",$D$4:$D$14)/COUNTIF($C$4:$C$14,"Open")

SUMPRODUCT generally only required for multi conditional tests (ie 2+ conditions) and/or where you are obliged to manipulate the source data as part of the calculation which as I read it is not the case here.

I wasn't sure what XL2003 has of AVERAGEIF, COUNTIF, SUMIF functions...
I played on sure

I wasn't sure what XL2003 has...

In terms of those inbuilt functions specific to 2007 seen on the board most regularly:

AVERAGEIF / AVERAGEIFS

SUMIFS

COUNTIFS

IFERROR

(Also worth remembering that ATP functions (NETWORKDAYS, WORKDAY, EDATE, EOMONTH, MROUND etc etc) require activation of ATP Add-In pre XL2007)