+ Reply to Thread
Results 1 to 4 of 4

Calculate average based on date and other criteria

  1. #1
    Kycajun
    Guest

    Calculate average based on date and other criteria

    I have a worksheet (sheet 1) which include the following columns:
    Audit Date.... Name.... File Score...
    (A3:A9949) (D3:D9949) (L3:L9949)

    On the next worksheet (sheet 2), I want to calculate a file score average
    for all entries belonging to a certain name and on or after a certain date
    shown on sheet 1. So for instance I want to calculate a file score average
    for Doe, John for all Audit Date entries on or after 7/1/2006.

    Any suggestions? Thanks!

  2. #2
    Biff
    Guest

    Re: Calculate average based on date and other criteria

    Hi!

    Try this:

    Entered as an array using the key combination of CTRL,SHIFT,ENTER:

    How to enter an array formula:

    http://cpearson.com/excel/array.htm

    =AVERAGE(IF((A3:A9949>=--"2006,7,1")*(D3:D9949="Doe, John"),L3:L9949))

    Better if you use cells to hold the criteria:

    M1 = 7/1/2006
    N1 = Doe, John

    =AVERAGE(IF((A3:A9949>=M1)*(D3:D9949=N1),L3:L9949))

    Biff

    "Kycajun" <Kycajun@discussions.microsoft.com> wrote in message
    news:AAE8A083-1DF5-4901-8316-40CA4FAD84DF@microsoft.com...
    >I have a worksheet (sheet 1) which include the following columns:
    > Audit Date.... Name.... File Score...
    > (A3:A9949) (D3:D9949) (L3:L9949)
    >
    > On the next worksheet (sheet 2), I want to calculate a file score average
    > for all entries belonging to a certain name and on or after a certain date
    > shown on sheet 1. So for instance I want to calculate a file score
    > average
    > for Doe, John for all Audit Date entries on or after 7/1/2006.
    >
    > Any suggestions? Thanks!




  3. #3
    Kycajun
    Guest

    Re: Calculate average based on date and other criteria

    Thanks! I am working on it now, so far it is not working, but I think I am
    close by using your formula. This may sound like a silly question, but how
    do you get the two dashes after the equal sign before the date. I tried the
    dash key and I don't think it is right, because they don't appear exactly as
    your does. Any idea?

    "Biff" wrote:

    > Hi!
    >
    > Try this:
    >
    > Entered as an array using the key combination of CTRL,SHIFT,ENTER:
    >
    > How to enter an array formula:
    >
    > http://cpearson.com/excel/array.htm
    >
    > =AVERAGE(IF((A3:A9949>=--"2006,7,1")*(D3:D9949="Doe, John"),L3:L9949))
    >
    > Better if you use cells to hold the criteria:
    >
    > M1 = 7/1/2006
    > N1 = Doe, John
    >
    > =AVERAGE(IF((A3:A9949>=M1)*(D3:D9949=N1),L3:L9949))
    >
    > Biff
    >
    > "Kycajun" <Kycajun@discussions.microsoft.com> wrote in message
    > news:AAE8A083-1DF5-4901-8316-40CA4FAD84DF@microsoft.com...
    > >I have a worksheet (sheet 1) which include the following columns:
    > > Audit Date.... Name.... File Score...
    > > (A3:A9949) (D3:D9949) (L3:L9949)
    > >
    > > On the next worksheet (sheet 2), I want to calculate a file score average
    > > for all entries belonging to a certain name and on or after a certain date
    > > shown on sheet 1. So for instance I want to calculate a file score
    > > average
    > > for Doe, John for all Audit Date entries on or after 7/1/2006.
    > >
    > > Any suggestions? Thanks!

    >
    >
    >


  4. #4
    Biff
    Guest

    Re: Calculate average based on date and other criteria

    Ooops!

    I goofed.

    >=AVERAGE(IF((A3:A9949>=--"2006,7,1")*(D3:D9949="Doe, John"),L3:L9949))


    Should be:

    =AVERAGE(IF((A3:A9949>=--"2006/7/1")*(D3:D9949="Doe, John"),L3:L9949))

    >how do you get the two dashes
    >I tried the dash key


    Those are just 2 consecutive minus signs. If you use cells to hold the
    criteria then you don't have to worry about that.

    M1 = 7/1/2006
    N1 = Doe, John

    =AVERAGE(IF((A3:A9949>=M1)*(D3:D9949=N1),L3:L9949))

    Biff

    "Kycajun" <Kycajun@discussions.microsoft.com> wrote in message
    news:C0C998AF-9EC8-47D8-938C-F2065A374203@microsoft.com...
    > Thanks! I am working on it now, so far it is not working, but I think I
    > am
    > close by using your formula. This may sound like a silly question, but
    > how
    > do you get the two dashes after the equal sign before the date. I tried
    > the
    > dash key and I don't think it is right, because they don't appear exactly
    > as
    > your does. Any idea?
    >
    > "Biff" wrote:
    >
    >> Hi!
    >>
    >> Try this:
    >>
    >> Entered as an array using the key combination of CTRL,SHIFT,ENTER:
    >>
    >> How to enter an array formula:
    >>
    >> http://cpearson.com/excel/array.htm
    >>
    >> =AVERAGE(IF((A3:A9949>=--"2006,7,1")*(D3:D9949="Doe, John"),L3:L9949))
    >>
    >> Better if you use cells to hold the criteria:
    >>
    >> M1 = 7/1/2006
    >> N1 = Doe, John
    >>
    >> =AVERAGE(IF((A3:A9949>=M1)*(D3:D9949=N1),L3:L9949))
    >>
    >> Biff
    >>
    >> "Kycajun" <Kycajun@discussions.microsoft.com> wrote in message
    >> news:AAE8A083-1DF5-4901-8316-40CA4FAD84DF@microsoft.com...
    >> >I have a worksheet (sheet 1) which include the following columns:
    >> > Audit Date.... Name.... File Score...
    >> > (A3:A9949) (D3:D9949) (L3:L9949)
    >> >
    >> > On the next worksheet (sheet 2), I want to calculate a file score
    >> > average
    >> > for all entries belonging to a certain name and on or after a certain
    >> > date
    >> > shown on sheet 1. So for instance I want to calculate a file score
    >> > average
    >> > for Doe, John for all Audit Date entries on or after 7/1/2006.
    >> >
    >> > Any suggestions? Thanks!

    >>
    >>
    >>




+ 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