formula to get an average based on a dropdown list.

[ialexander03]

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!!

[zbor]

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

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

[zbor]

If not try this:

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

[ialexander03]

Hi,

Thanx for you help so far, but both formulas return a message that states it contains a error.

do you have any other ideas?

[zbor]

try to replace ; with , due to different settings.

[ialexander03]

Thanx again for that,

I have tried the 1st option, the averageif statement, and i have replaced ; with , and now it is accpeting it, but shows #name? in the destination box!

[zbor]

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"))

[ialexander03]

i have done this, and still a error, this time, #value!

im sounding like a noob now, but what do the $ mean/do? and are they needed?

[NBVC]

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

[ialexander03]

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"))

thats awesome, with some tinkering ans some swearing, it now works!!

thank you so much!!!!

[DonkeyOte]

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.

[zbor]

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

[DonkeyOte]

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)