+ Reply to Thread
Results 1 to 9 of 9

formula to calculate the average of a range basing on condition

  1. #1
    Bob Phillips
    Guest

    Re: formula to calculate the average of a range basing on condition

    =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




  2. #2
    R.VENKATARAMAN
    Guest

    Re: formula to calculate the average of a range basing on condition



    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




  3. #3
    Mangesh Yadav
    Guest

    Re: formula to calculate the average of a range basing on condition

    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




  4. #4
    Bob Phillips
    Guest

    Re: formula to calculate the average of a range basing on condition

    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

    >
    >




  5. #5
    Krishna Mohan
    Guest

    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

  6. #6
    Bob Phillips
    Guest

    Re: formula to calculate the average of a range basing on condition

    =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




  7. #7
    R.VENKATARAMAN
    Guest

    Re: formula to calculate the average of a range basing on condition



    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




  8. #8
    Mangesh Yadav
    Guest

    Re: formula to calculate the average of a range basing on condition

    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




  9. #9
    Bob Phillips
    Guest

    Re: formula to calculate the average of a range basing on condition

    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

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1