Too Many Variables
 

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

Too Many Variables
 

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

"Heliocracy" wrote in message
...
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

Too Many Variables
 

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" wrote:

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

"Heliocracy" wrote in message
...
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

Too Many Variables
 

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

"Heliocracy" wrote in message
...
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" wrote:

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

"Heliocracy" wrote in message
...
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

Too Many Variables
 

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

"Bob Phillips" wrote:

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

"Heliocracy" wrote in message
...
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" wrote:

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

"Heliocracy" wrote in message
...
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

Too Many Variables
 

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

Heliocracy wrote:

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

"Bob Phillips" wrote:

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

"Heliocracy" wrote in message
...
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" wrote:

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

"Heliocracy" wrote in message
...
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







--

Dave Peterson

Too Many Variables
 

What nonsenseical answer is that?

--
HTH

Bob

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

"Heliocracy" wrote in message
...
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

"Bob Phillips" wrote:

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

"Heliocracy" wrote in message
...
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" wrote:

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

"Heliocracy" wrote in message
...
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