Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average with 2 criteria including month
In Excel 2000, I'm trying to create a formula whereby it finds case types
(ex: ABC) that close in a particular month (ex. January) and calculates the average length those cases were open. What's wrong w/ my formula? I get the #VALUE! displayed. I've also tried entering it as an array formula w/ the same result. =AVERAGE(IF((J17:J1500="ABC")*(MONTH(I17:I1500)=1) ,F17:1500)). Column J contains the case types, column I has the close dates (ex. 1/15/07), and col F calculates the # of days a case is open based on the open date (col G) & close date (col I). Now I know that I can use Autofilters for the the case types & close dates and the =SUBTOTAL(1,F17:F1500) formula, but I want to have the values for each of these months readily available. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average with 2 criteria including month
RS,
One way: =AVERAGE(IF(J17:J1500="ABC", IF(MONTH(I17:I1500)=1,F17:F1500))) Array entered with Ctrl-Shift-Enter HTH, Bernie MS Excel MVP "RS" wrote in message ... In Excel 2000, I'm trying to create a formula whereby it finds case types (ex: ABC) that close in a particular month (ex. January) and calculates the average length those cases were open. What's wrong w/ my formula? I get the #VALUE! displayed. I've also tried entering it as an array formula w/ the same result. =AVERAGE(IF((J17:J1500="ABC")*(MONTH(I17:I1500)=1) ,F17:1500)). Column J contains the case types, column I has the close dates (ex. 1/15/07), and col F calculates the # of days a case is open based on the open date (col G) & close date (col I). Now I know that I can use Autofilters for the the case types & close dates and the =SUBTOTAL(1,F17:F1500) formula, but I want to have the values for each of these months readily available. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average with 2 criteria including month
Works OK for me (once I changed F17:1500 to F17:F1500).
Something that can cause the #VALUE! error is having a #VALUE! error somewhere in your argument ranges that is then passed through. Have you checked that? In article , RS wrote: In Excel 2000, I'm trying to create a formula whereby it finds case types (ex: ABC) that close in a particular month (ex. January) and calculates the average length those cases were open. What's wrong w/ my formula? I get the #VALUE! displayed. I've also tried entering it as an array formula w/ the same result. =AVERAGE(IF((J17:J1500="ABC")*(MONTH(I17:I1500)=1) ,F17:1500)). Column J contains the case types, column I has the close dates (ex. 1/15/07), and col F calculates the # of days a case is open based on the open date (col G) & close date (col I). Now I know that I can use Autofilters for the the case types & close dates and the =SUBTOTAL(1,F17:F1500) formula, but I want to have the values for each of these months readily available. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average with 2 criteria including month
If you have text values for the months like January as opposed to for
instance Excel dates like 01/15/07 then you will get a value error just by using the month function Also the formula you posted has a typo, it should be (I presume) =AVERAGE(IF((J17:J1500="ABC")*(MONTH(I17:I1500)=1) ,F17:F1500)) and it needs to be entered with ctrl + shift & enter, -- Regards, Peo Sjoblom "RS" wrote in message ... In Excel 2000, I'm trying to create a formula whereby it finds case types (ex: ABC) that close in a particular month (ex. January) and calculates the average length those cases were open. What's wrong w/ my formula? I get the #VALUE! displayed. I've also tried entering it as an array formula w/ the same result. =AVERAGE(IF((J17:J1500="ABC")*(MONTH(I17:I1500)=1) ,F17:1500)). Column J contains the case types, column I has the close dates (ex. 1/15/07), and col F calculates the # of days a case is open based on the open date (col G) & close date (col I). Now I know that I can use Autofilters for the the case types & close dates and the =SUBTOTAL(1,F17:F1500) formula, but I want to have the values for each of these months readily available. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average with 2 criteria including month
Dear JE McGimpsey,
Thanks for your response. You're right. There were actually 2 items (#VALUE! and #NUM!) which were causing my formula to not work. The F1500 omission was actually just a typo in my post, but thanks for pointing it out to me. However, the formula is still not displaying the correct average. The value it is currently showing is greater than the value I calculate when using the autofilters for the case type (ABC) and closing date (col J = 1/1/07 And <2/1/07. When I do this, there are 25 cases displayed via =SUBTOTAL(3,J17:J1500) and the average of this filtered subtotal is 94 via =SUBTOTAL(1,F17:F1500). This value is the correct one that I should be getting w/ our formula. I noticed that my client had deleted one of the formulas in col F (# days). Interestingly, when I copied the formula from the previous cell, the value in the cell containing the average formula changed even though this row didn't have a close date entered. Just to inform you, not all rows have close dates, though col F (# days) can contain a valid # because I have an additional column which has projected close dates (col H). This shouldn't affect the calculation because this date is in a separate column. Also, 3 of the rows have a "-" in them because I did a custom format whereby if the value is blank, it displays "-". However, none of these 3 have close dates of January in column F. I did a little test whereby I autofiltered for ABC and then deleted all these ABC values from col J except 1 (in Row 860) that had a blank close date (col I) but a projected close date (col H) of 6/30/07 giving a value of 111 in col F. Sure enough, the average formula displayed 111 even though there is no close date in the month of January for this row. What's wrong w/ the formula? "JE McGimpsey" wrote: Works OK for me (once I changed F17:1500 to F17:F1500). Something that can cause the #VALUE! error is having a #VALUE! error somewhere in your argument ranges that is then passed through. Have you checked that? In article , RS wrote: In Excel 2000, I'm trying to create a formula whereby it finds case types (ex: ABC) that close in a particular month (ex. January) and calculates the average length those cases were open. What's wrong w/ my formula? I get the #VALUE! displayed. I've also tried entering it as an array formula w/ the same result. =AVERAGE(IF((J17:J1500="ABC")*(MONTH(I17:I1500)=1) ,F17:1500)). Column J contains the case types, column I has the close dates (ex. 1/15/07), and col F calculates the # of days a case is open based on the open date (col G) & close date (col I). Now I know that I can use Autofilters for the the case types & close dates and the =SUBTOTAL(1,F17:F1500) formula, but I want to have the values for each of these months readily available. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average with 2 criteria including month
Dear Bernie,
Thanks for your response. You're formula also worked once I removed the #VALUE! and #NUM! from the spreadsheet. Interestingly, even though both values were present, your formula displayed the #NUM! value even though the #VALUE! appeared earlier on in the spreadsheet. Removing the #NUM! error allowed the formula to perform the calculation while ignoring the #VALUE!. However, as I noted in my post to JE McGimpsey, the formula is still not correctly calculating the average. Do you know what's wrong? "Bernie Deitrick" wrote: RS, One way: =AVERAGE(IF(J17:J1500="ABC", IF(MONTH(I17:I1500)=1,F17:F1500))) Array entered with Ctrl-Shift-Enter HTH, Bernie MS Excel MVP "RS" wrote in message ... In Excel 2000, I'm trying to create a formula whereby it finds case types (ex: ABC) that close in a particular month (ex. January) and calculates the average length those cases were open. What's wrong w/ my formula? I get the #VALUE! displayed. I've also tried entering it as an array formula w/ the same result. =AVERAGE(IF((J17:J1500="ABC")*(MONTH(I17:I1500)=1) ,F17:1500)). Column J contains the case types, column I has the close dates (ex. 1/15/07), and col F calculates the # of days a case is open based on the open date (col G) & close date (col I). Now I know that I can use Autofilters for the the case types & close dates and the =SUBTOTAL(1,F17:F1500) formula, but I want to have the values for each of these months readily available. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average with 2 criteria including month
Dear Peo,
I don't have text values for the dates, they are entered in the m/d/yy format. As JE McGimpsey noted, having a #VALUE! w/in the column would give me this error. As I told him, the F1500 was just a typo in my post not in the actual formula. Thanks for noting it though. If you read my latest posts, the issue isn't quite solved yet. "Peo Sjoblom" wrote: If you have text values for the months like January as opposed to for instance Excel dates like 01/15/07 then you will get a value error just by using the month function Also the formula you posted has a typo, it should be (I presume) =AVERAGE(IF((J17:J1500="ABC")*(MONTH(I17:I1500)=1) ,F17:F1500)) and it needs to be entered with ctrl + shift & enter, -- Regards, Peo Sjoblom "RS" wrote in message ... In Excel 2000, I'm trying to create a formula whereby it finds case types (ex: ABC) that close in a particular month (ex. January) and calculates the average length those cases were open. What's wrong w/ my formula? I get the #VALUE! displayed. I've also tried entering it as an array formula w/ the same result. =AVERAGE(IF((J17:J1500="ABC")*(MONTH(I17:I1500)=1) ,F17:1500)). Column J contains the case types, column I has the close dates (ex. 1/15/07), and col F calculates the # of days a case is open based on the open date (col G) & close date (col I). Now I know that I can use Autofilters for the the case types & close dates and the =SUBTOTAL(1,F17:F1500) formula, but I want to have the values for each of these months readily available. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average with 2 criteria including month
For one thing
MONTH(I17:I1500)=1 will include any cells that are blanks since blank cells are equal to zero and Jan zero 1900 is when Excel starts recognizing dates, so another alternative would be =AVERAGE(IF((J17:J1500="ABC")*(I17:I1500=--"2007-01-01")*(I17:I1500<=--"2007-01-31"),F17:F1500)) array entered also if you want to make sure the count is correct vis-à-vis =SUMPRODUCT(--(J17:J1500="ABC"),--(I17:I1500=--"2007-01-01"),--(I17:I1500<=--"2007-01-31")) should return 25 using your criteria of course the hardcoded dates for Jan 01 should be replaced with cell where you type in the dates, and so should "ABC" be, that way you don't need to edit the formula when you change dates and criteria etc -- Regards, Peo Sjoblom RS" wrote in message ... Dear JE McGimpsey, Thanks for your response. You're right. There were actually 2 items (#VALUE! and #NUM!) which were causing my formula to not work. The F1500 omission was actually just a typo in my post, but thanks for pointing it out to me. However, the formula is still not displaying the correct average. The value it is currently showing is greater than the value I calculate when using the autofilters for the case type (ABC) and closing date (col J = 1/1/07 And <2/1/07. When I do this, there are 25 cases displayed via =SUBTOTAL(3,J17:J1500) and the average of this filtered subtotal is 94 via =SUBTOTAL(1,F17:F1500). This value is the correct one that I should be getting w/ our formula. I noticed that my client had deleted one of the formulas in col F (# days). Interestingly, when I copied the formula from the previous cell, the value in the cell containing the average formula changed even though this row didn't have a close date entered. Just to inform you, not all rows have close dates, though col F (# days) can contain a valid # because I have an additional column which has projected close dates (col H). This shouldn't affect the calculation because this date is in a separate column. Also, 3 of the rows have a "-" in them because I did a custom format whereby if the value is blank, it displays "-". However, none of these 3 have close dates of January in column F. I did a little test whereby I autofiltered for ABC and then deleted all these ABC values from col J except 1 (in Row 860) that had a blank close date (col I) but a projected close date (col H) of 6/30/07 giving a value of 111 in col F. Sure enough, the average formula displayed 111 even though there is no close date in the month of January for this row. What's wrong w/ the formula? "JE McGimpsey" wrote: Works OK for me (once I changed F17:1500 to F17:F1500). Something that can cause the #VALUE! error is having a #VALUE! error somewhere in your argument ranges that is then passed through. Have you checked that? In article , RS wrote: In Excel 2000, I'm trying to create a formula whereby it finds case types (ex: ABC) that close in a particular month (ex. January) and calculates the average length those cases were open. What's wrong w/ my formula? I get the #VALUE! displayed. I've also tried entering it as an array formula w/ the same result. =AVERAGE(IF((J17:J1500="ABC")*(MONTH(I17:I1500)=1) ,F17:1500)). Column J contains the case types, column I has the close dates (ex. 1/15/07), and col F calculates the # of days a case is open based on the open date (col G) & close date (col I). Now I know that I can use Autofilters for the the case types & close dates and the =SUBTOTAL(1,F17:F1500) formula, but I want to have the values for each of these months readily available. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average with 2 criteria including month
Dear Peo,
Thanks for your suggestion, I had no idea Excel would treat a blank cell as Jan 1900. Seems kind of silly since there's no data in the cell. I could understand if someone entered in Jan 1900. Anyway, using your formula gives me the correct average of 94. I already had a formula that was displaying the correct count and this formula also made use of references to cells containing the service type and date (much as you suggested). I simply left out these references in my post to make it easier to understand. The formula I was using to double-check the count (similar to yours) is: =SUMPRODUCT(--(TEXT($I$17:$I$1500,"mmmyyyy")=TEXT(AD$2,"mmmyyyy" )),--($J$17:$J$1500=$AQ12)) where AD$2 refers to a date formatted cell and $AQ12 = ABC. Modifying your formula to include the references I used in my SUMPRODUCT equation gives the following array-entered formula for calculating averages correctly: =AVERAGE(IF(($J$17:$J$1500=$AQ12)*(TEXT($I$17:$I$1 500,"mmmyyyy")=TEXT(AD$2,"mmmyyyy")),F17:F1500)) Using this formula, I can simply copy this formula across a grid & make a table for the different service types for each month. Thanks so much for your assistance! "Peo Sjoblom" wrote: For one thing MONTH(I17:I1500)=1 will include any cells that are blanks since blank cells are equal to zero and Jan zero 1900 is when Excel starts recognizing dates, so another alternative would be =AVERAGE(IF((J17:J1500="ABC")*(I17:I1500=--"2007-01-01")*(I17:I1500<=--"2007-01-31"),F17:F1500)) array entered also if you want to make sure the count is correct vis-Ã*-vis =SUMPRODUCT(--(J17:J1500="ABC"),--(I17:I1500=--"2007-01-01"),--(I17:I1500<=--"2007-01-31")) should return 25 using your criteria of course the hardcoded dates for Jan 01 should be replaced with cell where you type in the dates, and so should "ABC" be, that way you don't need to edit the formula when you change dates and criteria etc -- Regards, Peo Sjoblom RS" wrote in message ... Dear JE McGimpsey, Thanks for your response. You're right. There were actually 2 items (#VALUE! and #NUM!) which were causing my formula to not work. The F1500 omission was actually just a typo in my post, but thanks for pointing it out to me. However, the formula is still not displaying the correct average. The value it is currently showing is greater than the value I calculate when using the autofilters for the case type (ABC) and closing date (col J = 1/1/07 And <2/1/07. When I do this, there are 25 cases displayed via =SUBTOTAL(3,J17:J1500) and the average of this filtered subtotal is 94 via =SUBTOTAL(1,F17:F1500). This value is the correct one that I should be getting w/ our formula. I noticed that my client had deleted one of the formulas in col F (# days). Interestingly, when I copied the formula from the previous cell, the value in the cell containing the average formula changed even though this row didn't have a close date entered. Just to inform you, not all rows have close dates, though col F (# days) can contain a valid # because I have an additional column which has projected close dates (col H). This shouldn't affect the calculation because this date is in a separate column. Also, 3 of the rows have a "-" in them because I did a custom format whereby if the value is blank, it displays "-". However, none of these 3 have close dates of January in column F. I did a little test whereby I autofiltered for ABC and then deleted all these ABC values from col J except 1 (in Row 860) that had a blank close date (col I) but a projected close date (col H) of 6/30/07 giving a value of 111 in col F. Sure enough, the average formula displayed 111 even though there is no close date in the month of January for this row. What's wrong w/ the formula? "JE McGimpsey" wrote: Works OK for me (once I changed F17:1500 to F17:F1500). Something that can cause the #VALUE! error is having a #VALUE! error somewhere in your argument ranges that is then passed through. Have you checked that? In article , RS wrote: In Excel 2000, I'm trying to create a formula whereby it finds case types (ex: ABC) that close in a particular month (ex. January) and calculates the average length those cases were open. What's wrong w/ my formula? I get the #VALUE! displayed. I've also tried entering it as an array formula w/ the same result. =AVERAGE(IF((J17:J1500="ABC")*(MONTH(I17:I1500)=1) ,F17:1500)). Column J contains the case types, column I has the close dates (ex. 1/15/07), and col F calculates the # of days a case is open based on the open date (col G) & close date (col I). Now I know that I can use Autofilters for the the case types & close dates and the =SUBTOTAL(1,F17:F1500) formula, but I want to have the values for each of these months readily available. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Fill days of the month (not including weekends) | Excel Worksheet Functions | |||
How do I count frequency based on 2 criteria (including month) | Excel Worksheet Functions | |||
Average range including blank cells: #DIV/0! | Excel Worksheet Functions | |||
in excel, how do i get an average without including the zeros? | Excel Worksheet Functions | |||
How do I average a column without including zeros | Excel Discussion (Misc queries) |