Too Many Variables

[Guest]

Okay I've got dates in column A, column L has the number of days since the
column A date, and column N has text that says either "maint*" or "ent*."
I'm using the following formula to calculate the average number of days in
column L for specific dates in column A:

=(SUMIF(Active!A10:A1000,"<39052",Active!L10:L1000))/(COUNTIF(Active!A10:A1000,"<39052"))

It seems to work fine--I get the average number of days that items dated
before 12/2006 have listed in column L.

Now, I need to get the average number of days listed in column L for records
where both the date in column A is before 12/2006, AND the text in column M
is "maint*." It's too many variables to get my head wrapped around. Can
anyone please help? Hope this is clear enough...

Thanks,
Mike

 

[Bob Phillips]

=AVERAGE(IF((Active!A10:A1000<--"2006-12-01")*(Active!M10:M1000="maint*"),Active!L10:L1000))

which is an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks, that seems to work. Can you give me a better formulation for the
formula I provided in my original post (the one that does the same thing
without worrying about what's in column M)? I really appreciate your help.

Thanks,
Mike

 

[Bob Phillips]

=AVERAGE(IF(Active!A10:A1000,<--"2006-12-01",Active!L10:L1000))

again an array formula

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

I'm afraid that formula returns an error. When I remove the comma between
A1000 and <--, it accepts the formula but gives me a non-sensical answer. I
am entering it as an array formula.

Thanks, Mike

 

[Dave Peterson]

Did you remember to enter the formula using ctrl-shift-enter?

What kind of error do you see?

Are there errors in A10:A1000 or L10:L1000?

Are you using USA English version of excel?

Maybe you don't use commas to separate the arguments?? (But that would have
caused an error in the original suggestion, too.)

 

[Bob Phillips]

What nonsenseical answer is that?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)