| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
formula to calculate the average of a range basing on condition
Hi,
I have a situation like this below.. 18-Feb-02 02-Jun-04 26-Jul-02 08-Jun-04 29-Jul-03 10-Jun-04 22-Oct-03 05-Jun-04 01-Nov-03 10-Jun-04 11-Nov-03 14-Jun-04 18-Nov-03 04-Jun-04 20-Dec-03 21-May-04 30-Mar-04 10-Jun-04 08-May-04 22-Jun-04 29-May-04 15-Aug-04 11-Jun-04 11-Jul-04 12-Jun-04 16-Jun-04 22-Jun-04 28-Jun-04 24-Jun-04 26-Jun-04 10-Dec-04 23-Dec-04 28-Aug-04 01-Sep-04 28-Sep-04 29-Sep-04 08-Dec-04 22-Dec-04 20-Aug-04 29-Aug-04 25-Aug-04 31-Aug-04 30-Aug-04 10-Sep-04 03-Jan-05 10-Jan-05 27-Sep-04 05-Oct-04 30-Sep-04 07-Oct-04 03-Oct-04 04-Oct-04 The above are two columns of excel. I want to calculate the average of the difference for each month in another work sheet like bwlow Month Difference.. Can some one help me on how I can do that |
|
#2
|
|||
|
|||
|
=SUMPRODUCT(--(A1:A100<""),--(B1:B100<""),(B1:B100-A1:A100))/SUMPRODUCT(--
(A1:A100<""),--(B1:B100<"")) -- HTH Bob Phillips "Krishna Mohan" wrote in message ... Hi, I have a situation like this below.. 18-Feb-02 02-Jun-04 26-Jul-02 08-Jun-04 29-Jul-03 10-Jun-04 22-Oct-03 05-Jun-04 01-Nov-03 10-Jun-04 11-Nov-03 14-Jun-04 18-Nov-03 04-Jun-04 20-Dec-03 21-May-04 30-Mar-04 10-Jun-04 08-May-04 22-Jun-04 29-May-04 15-Aug-04 11-Jun-04 11-Jul-04 12-Jun-04 16-Jun-04 22-Jun-04 28-Jun-04 24-Jun-04 26-Jun-04 10-Dec-04 23-Dec-04 28-Aug-04 01-Sep-04 28-Sep-04 29-Sep-04 08-Dec-04 22-Dec-04 20-Aug-04 29-Aug-04 25-Aug-04 31-Aug-04 30-Aug-04 10-Sep-04 03-Jan-05 10-Jan-05 27-Sep-04 05-Oct-04 30-Sep-04 07-Oct-04 03-Oct-04 04-Oct-04 The above are two columns of excel. I want to calculate the average of the difference for each month in another work sheet like bwlow Month Difference.. Can some one help me on how I can do that |
|
#3
|
|||
|
|||
|
your data is A! to A14 abd B1 to B14 try this in any cell =AVERAGE(A1:A14-B1:B14) this is array formula . so DONT hit enter but hit control+shift+enter I have used numbers instead of dates. I expect this to work even with dates provided cells are in date foramt. Krishna Mohan wrote in message ... Hi, I have a situation like this below.. 18-Feb-02 02-Jun-04 26-Jul-02 08-Jun-04 29-Jul-03 10-Jun-04 22-Oct-03 05-Jun-04 01-Nov-03 10-Jun-04 11-Nov-03 14-Jun-04 18-Nov-03 04-Jun-04 20-Dec-03 21-May-04 30-Mar-04 10-Jun-04 08-May-04 22-Jun-04 29-May-04 15-Aug-04 11-Jun-04 11-Jul-04 12-Jun-04 16-Jun-04 22-Jun-04 28-Jun-04 24-Jun-04 26-Jun-04 10-Dec-04 23-Dec-04 28-Aug-04 01-Sep-04 28-Sep-04 29-Sep-04 08-Dec-04 22-Dec-04 20-Aug-04 29-Aug-04 25-Aug-04 31-Aug-04 30-Aug-04 10-Sep-04 03-Jan-05 10-Jan-05 27-Sep-04 05-Oct-04 30-Sep-04 07-Oct-04 03-Oct-04 04-Oct-04 The above are two columns of excel. I want to calculate the average of the difference for each month in another work sheet like bwlow Month Difference.. Can some one help me on how I can do that |
|
#4
|
|||
|
|||
|
Problem with that is that it averages blanks as well. I also noticed that
the last date is inverted in comparison to the others. Both can be overcome with =AVERAGE(IF(B1:B34-A1:A34<0,ABS(B1:B34-A1:A34))) which is still an array form ula. -- HTH Bob Phillips "R.VENKATARAMAN" wrote in message ... your data is A! to A14 abd B1 to B14 try this in any cell =AVERAGE(A1:A14-B1:B14) this is array formula . so DONT hit enter but hit control+shift+enter I have used numbers instead of dates. I expect this to work even with dates provided cells are in date foramt. Krishna Mohan wrote in message ... Hi, I have a situation like this below.. 18-Feb-02 02-Jun-04 26-Jul-02 08-Jun-04 29-Jul-03 10-Jun-04 22-Oct-03 05-Jun-04 01-Nov-03 10-Jun-04 11-Nov-03 14-Jun-04 18-Nov-03 04-Jun-04 20-Dec-03 21-May-04 30-Mar-04 10-Jun-04 08-May-04 22-Jun-04 29-May-04 15-Aug-04 11-Jun-04 11-Jul-04 12-Jun-04 16-Jun-04 22-Jun-04 28-Jun-04 24-Jun-04 26-Jun-04 10-Dec-04 23-Dec-04 28-Aug-04 01-Sep-04 28-Sep-04 29-Sep-04 08-Dec-04 22-Dec-04 20-Aug-04 29-Aug-04 25-Aug-04 31-Aug-04 30-Aug-04 10-Sep-04 03-Jan-05 10-Jan-05 27-Sep-04 05-Oct-04 30-Sep-04 07-Oct-04 03-Oct-04 04-Oct-04 The above are two columns of excel. I want to calculate the average of the difference for each month in another work sheet like bwlow Month Difference.. Can some one help me on how I can do that |
|
#5
|
|||
|
|||
|
Try the following:
Lets say you have your two columns in columns A and B. In C enter the formula: =MONTH(A1) and drag down to copy. In D, enter =B1-A1 In E1 to E12, enter 1 to 12 for each month In F, enter =IF(COUNTIF($C$1:$C$26,E1)=0,"",SUM(--($C$1:$C$26=E1)*($D$1:$D$26))/COUNTIF( $C$1:$C$26,E1)) press control shift enter and drag down Mangesh "Krishna Mohan" wrote in message ... Hi, I have a situation like this below.. 18-Feb-02 02-Jun-04 26-Jul-02 08-Jun-04 29-Jul-03 10-Jun-04 22-Oct-03 05-Jun-04 01-Nov-03 10-Jun-04 11-Nov-03 14-Jun-04 18-Nov-03 04-Jun-04 20-Dec-03 21-May-04 30-Mar-04 10-Jun-04 08-May-04 22-Jun-04 29-May-04 15-Aug-04 11-Jun-04 11-Jul-04 12-Jun-04 16-Jun-04 22-Jun-04 28-Jun-04 24-Jun-04 26-Jun-04 10-Dec-04 23-Dec-04 28-Aug-04 01-Sep-04 28-Sep-04 29-Sep-04 08-Dec-04 22-Dec-04 20-Aug-04 29-Aug-04 25-Aug-04 31-Aug-04 30-Aug-04 10-Sep-04 03-Jan-05 10-Jan-05 27-Sep-04 05-Oct-04 30-Sep-04 07-Oct-04 03-Oct-04 04-Oct-04 The above are two columns of excel. I want to calculate the average of the difference for each month in another work sheet like bwlow Month Difference.. Can some one help me on how I can do that |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Changing a range of an array in a SUMPRODUCT formula gives a #N/A error | Excel Discussion (Misc queries) | |||
| can a formula check for a certain value in a range? | Excel Discussion (Misc queries) | |||
| Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
| how do I make a formula NOT change when the data range is moved? | Excel Discussion (Misc queries) | |||
| Average Formula | Excel Worksheet Functions | |||
Linear Mode
