formula to calculate the average of a range basing on condition

Bob Phillips · 09-06-2005, 03:05 AM

=SUMPRODUCT(--(A1:A100<>""),--(B1:B100<>""),(B1:B100-A1:A100))/SUMPRODUCT(--
(A1:A100<>""),--(B1:B100<>""))

--
HTH

Bob Phillips

"Krishna Mohan" <KrishnaMohan@discussions.microsoft.com> wrote in message
news:511F2E1C-D1FC-465A-800E-83EDD502039C@microsoft.com...
> 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

R.VENKATARAMAN · 09-06-2005, 03:05 AM

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 <KrishnaMohan@discussions.microsoft.com> wrote in message
news:511F2E1C-D1FC-465A-800E-83EDD502039C@microsoft.com...
> 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

Mangesh Yadav · 09-06-2005, 03:05 AM

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" <KrishnaMohan@discussions.microsoft.com> wrote in message
news:511F2E1C-D1FC-465A-800E-83EDD502039C@microsoft.com...
> 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

Bob Phillips · 09-06-2005, 03:05 AM

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" <vram26@vsnl$$$.net> wrote in message
news:OGk7flkdFHA.584@TK2MSFTNGP15.phx.gbl...
>
>
> 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 <KrishnaMohan@discussions.microsoft.com> wrote in message
> news:511F2E1C-D1FC-465A-800E-83EDD502039C@microsoft.com...
> > 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
>
>

Krishna Mohan · 09-06-2005, 10:05 AM

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

Bob Phillips · 09-06-2005, 10:05 AM

=SUMPRODUCT(--(A1:A100<>""),--(B1:B100<>""),(B1:B100-A1:A100))/SUMPRODUCT(--
(A1:A100<>""),--(B1:B100<>""))

--
HTH

Bob Phillips

"Krishna Mohan" <KrishnaMohan@discussions.microsoft.com> wrote in message
news:511F2E1C-D1FC-465A-800E-83EDD502039C@microsoft.com...
> 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

R.VENKATARAMAN · 09-06-2005, 10:05 AM

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 <KrishnaMohan@discussions.microsoft.com> wrote in message
news:511F2E1C-D1FC-465A-800E-83EDD502039C@microsoft.com...
> 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

Mangesh Yadav · 09-06-2005, 10:05 AM

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" <KrishnaMohan@discussions.microsoft.com> wrote in message
news:511F2E1C-D1FC-465A-800E-83EDD502039C@microsoft.com...
> 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

Bob Phillips · 09-06-2005, 10:05 AM

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" <vram26@vsnl$$$.net> wrote in message
news:OGk7flkdFHA.584@TK2MSFTNGP15.phx.gbl...
>
>
> 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 <KrishnaMohan@discussions.microsoft.com> wrote in message
> news:511F2E1C-D1FC-465A-800E-83EDD502039C@microsoft.com...
> > 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
>
>