Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I perform a conditional average with exclusions?
I am trying to perform a conditional average with the following conditions:
ColumB is the document type, (i.e., change of address). Column E is the source of the information (i.e., Internal, mail, etc). Column H is the network day turn around (formula NETWORKDAYS). Document_Type Source TAT Address Change T 4 Address Change C 10 Address Change I 1 I am trying to get an average of all TATs for address changes that do NOT include source codes C or I. I am using Excel 2000, and I can get conditional sum to work with those parameters, and I can get a basic "average if" function to work, but the minute I try to exclude C and I, I get a value of 0, which is incorrect. Anyone have any suggestions on a formula? Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I perform a conditional average with exclusions?
=SUM((B2:B100="Address
Change")*(E2:E100<"C")*(E2:E100<"I")*(H2:H100))/COUNT((B2:B100="Address Change")*(E2:E100<"C")*(E2:E100<"I")) Adjust range sizes as needed. Input formula as an array (Ctrl+Shift+Enter) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Sandre" wrote: I am trying to perform a conditional average with the following conditions: ColumB is the document type, (i.e., change of address). Column E is the source of the information (i.e., Internal, mail, etc). Column H is the network day turn around (formula NETWORKDAYS). Document_Type Source TAT Address Change T 4 Address Change C 10 Address Change I 1 I am trying to get an average of all TATs for address changes that do NOT include source codes C or I. I am using Excel 2000, and I can get conditional sum to work with those parameters, and I can get a basic "average if" function to work, but the minute I try to exclude C and I, I get a value of 0, which is incorrect. Anyone have any suggestions on a formula? Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I perform a conditional average with exclusions?
Wow, that was a great start. I never thought of manually performing the calc
instead of an average if. Thank you!! The only problem I ran into is that not all of column B is address changes. I got a value error when I used this formula, so I am thinking that it's maybe because there are other things in the same column (that will also need to be averaged). I have bank changes, cancellations, and other categories of transactions, so I know there has to be an "If" in there somewhere. Thank you for all the help!! "Luke M" wrote: =SUM((B2:B100="Address Change")*(E2:E100<"C")*(E2:E100<"I")*(H2:H100))/COUNT((B2:B100="Address Change")*(E2:E100<"C")*(E2:E100<"I")) Adjust range sizes as needed. Input formula as an array (Ctrl+Shift+Enter) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Sandre" wrote: I am trying to perform a conditional average with the following conditions: ColumB is the document type, (i.e., change of address). Column E is the source of the information (i.e., Internal, mail, etc). Column H is the network day turn around (formula NETWORKDAYS). Document_Type Source TAT Address Change T 4 Address Change C 10 Address Change I 1 I am trying to get an average of all TATs for address changes that do NOT include source codes C or I. I am using Excel 2000, and I can get conditional sum to work with those parameters, and I can get a basic "average if" function to work, but the minute I try to exclude C and I, I get a value of 0, which is incorrect. Anyone have any suggestions on a formula? Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I perform a conditional average with exclusions?
Maybe this
=AVERAGE(IF(B2:B100="Address Change",IF(E2:E100<"T",IF(E2:E100<"I",H2:H100))) ) This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "Sandre" wrote: Wow, that was a great start. I never thought of manually performing the calc instead of an average if. Thank you!! The only problem I ran into is that not all of column B is address changes. I got a value error when I used this formula, so I am thinking that it's maybe because there are other things in the same column (that will also need to be averaged). I have bank changes, cancellations, and other categories of transactions, so I know there has to be an "If" in there somewhere. Thank you for all the help!! "Luke M" wrote: =SUM((B2:B100="Address Change")*(E2:E100<"C")*(E2:E100<"I")*(H2:H100))/COUNT((B2:B100="Address Change")*(E2:E100<"C")*(E2:E100<"I")) Adjust range sizes as needed. Input formula as an array (Ctrl+Shift+Enter) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Sandre" wrote: I am trying to perform a conditional average with the following conditions: ColumB is the document type, (i.e., change of address). Column E is the source of the information (i.e., Internal, mail, etc). Column H is the network day turn around (formula NETWORKDAYS). Document_Type Source TAT Address Change T 4 Address Change C 10 Address Change I 1 I am trying to get an average of all TATs for address changes that do NOT include source codes C or I. I am using Excel 2000, and I can get conditional sum to work with those parameters, and I can get a basic "average if" function to work, but the minute I try to exclude C and I, I get a value of 0, which is incorrect. Anyone have any suggestions on a formula? Thanks! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I perform a conditional average with exclusions?
Hmm, the first part of formula is checking that, it should only be including
the "Address changes". Make sure that the arrays are all the same length, (and not entire column references, e.g. E:E). Also, it must be entered as an array (Ctrl+Shift+Enter) so that the {} brackets appear around formula. Mike H's formula appears shorter, I wasn't sure if AVERAGE included the FALSE values in its calculation. From a time/calculation saving standpoint, I'd recommed his formula then. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Sandre" wrote: Wow, that was a great start. I never thought of manually performing the calc instead of an average if. Thank you!! The only problem I ran into is that not all of column B is address changes. I got a value error when I used this formula, so I am thinking that it's maybe because there are other things in the same column (that will also need to be averaged). I have bank changes, cancellations, and other categories of transactions, so I know there has to be an "If" in there somewhere. Thank you for all the help!! "Luke M" wrote: =SUM((B2:B100="Address Change")*(E2:E100<"C")*(E2:E100<"I")*(H2:H100))/COUNT((B2:B100="Address Change")*(E2:E100<"C")*(E2:E100<"I")) Adjust range sizes as needed. Input formula as an array (Ctrl+Shift+Enter) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Sandre" wrote: I am trying to perform a conditional average with the following conditions: ColumB is the document type, (i.e., change of address). Column E is the source of the information (i.e., Internal, mail, etc). Column H is the network day turn around (formula NETWORKDAYS). Document_Type Source TAT Address Change T 4 Address Change C 10 Address Change I 1 I am trying to get an average of all TATs for address changes that do NOT include source codes C or I. I am using Excel 2000, and I can get conditional sum to work with those parameters, and I can get a basic "average if" function to work, but the minute I try to exclude C and I, I get a value of 0, which is incorrect. Anyone have any suggestions on a formula? Thanks! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I perform a conditional average with exclusions?
That's it, thank you!!!
Here's the final formula with ALL of the parameters factored in: =IF(C11=0,0,AVERAGE(IF('\\XXXXXX\[TAT Report.xls]Sheet1'!$B$1:$B$20000="Address Change",IF('\\Ias-fs\pos\Public\KSKJ_Life\Reports\[TAT Report.xls]Sheet1'!$E$1:$E$20000<"C",IF('\\XXXXXX\[TAT Report.xls]Sheet1'!$E$1:$E$20000<"I",'\\XXXXXX\[TAT Report.xls]Sheet1'!$H$1:$H$20000))))) Thank you both, you are awesome :D "Luke M" wrote: Hmm, the first part of formula is checking that, it should only be including the "Address changes". Make sure that the arrays are all the same length, (and not entire column references, e.g. E:E). Also, it must be entered as an array (Ctrl+Shift+Enter) so that the {} brackets appear around formula. Mike H's formula appears shorter, I wasn't sure if AVERAGE included the FALSE values in its calculation. From a time/calculation saving standpoint, I'd recommed his formula then. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Sandre" wrote: Wow, that was a great start. I never thought of manually performing the calc instead of an average if. Thank you!! The only problem I ran into is that not all of column B is address changes. I got a value error when I used this formula, so I am thinking that it's maybe because there are other things in the same column (that will also need to be averaged). I have bank changes, cancellations, and other categories of transactions, so I know there has to be an "If" in there somewhere. Thank you for all the help!! "Luke M" wrote: =SUM((B2:B100="Address Change")*(E2:E100<"C")*(E2:E100<"I")*(H2:H100))/COUNT((B2:B100="Address Change")*(E2:E100<"C")*(E2:E100<"I")) Adjust range sizes as needed. Input formula as an array (Ctrl+Shift+Enter) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Sandre" wrote: I am trying to perform a conditional average with the following conditions: ColumB is the document type, (i.e., change of address). Column E is the source of the information (i.e., Internal, mail, etc). Column H is the network day turn around (formula NETWORKDAYS). Document_Type Source TAT Address Change T 4 Address Change C 10 Address Change I 1 I am trying to get an average of all TATs for address changes that do NOT include source codes C or I. I am using Excel 2000, and I can get conditional sum to work with those parameters, and I can get a basic "average if" function to work, but the minute I try to exclude C and I, I get a value of 0, which is incorrect. Anyone have any suggestions on a formula? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Perform action if value in cell - conditional VB stmt | Excel Discussion (Misc queries) | |||
Validate Whole Number Series with Exclusions | Excel Discussion (Misc queries) | |||
COUNTIF using exclusions | Excel Worksheet Functions | |||
COUNTIF using exclusions | Excel Worksheet Functions | |||
Formula Exclusions? | Excel Discussion (Misc queries) |