| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| More than 1 Variables | Excel Discussion (Misc queries) | |||
| Variables in VBA | Excel Discussion (Misc queries) | |||
| Using variables . . . | Excel Discussion (Misc queries) | |||
| Too many variables? | Excel Worksheet Functions | |||
| SUM IF and two variables | Excel Worksheet Functions | |||
Hybrid Mode
