+ Reply to Thread
Results 1 to 14 of 14

Can you do a average with criteria from another column?

  1. #1
    Registered User
    Join Date
    10-10-2006
    Posts
    14

    Can you do a average with criteria from another column?

    I have information I'm trying to evaluate. Average Close time. but I need to evaluate it for each month. Forinstance if my average close time was B. and the close date was c. I would need the average(b:b) but only from fields that had a close date of July in column C. is this possible? I hate to have to break up the data set 12 times. (i'm reporting individually for each month of the year so we can see trends.)

  2. #2
    Registered User
    Join Date
    01-09-2006
    Posts
    7

    averaging

    you could always try what i do (i'm no genius at this stuff, but it always works):
    do a sumif divided by a countif. in other words, use the definition of averaging.

    example: sumif(column b stuff, criteria, column c stuff)/countif(column b stuff, criteria)

    mine specifically looks like: sumif($b$2:$b$20,"G",c2:c20)/countif($b$2:$b$20,"G")

    it sums the scores (in column c) if the student is a girl (determined by a "G" or "B" in column b) and then divides by the number of girls it counts. i have another one for boys. it works.

  3. #3
    Forum Contributor kraljb's Avatar
    Join Date
    05-26-2004
    Location
    Illinois
    MS-Off Ver
    2007 (recent change)
    Posts
    256
    Try this...

    =SUMPRODUCT(--(TEXT($C:$C,"mmmm")="July"),$B:$B)/SUMPRODUCT(--(TEXT($C:$C,"mmmm")="July"))

    Hope it helps...
    John

  4. #4
    Registered User
    Join Date
    10-10-2006
    Posts
    14
    thank you Kraljb. Ok one last thing though. I'm trying to make this report auto update. so each entry has to take this month - 12 for the first average, then this month -11 for the next. I've been fighting this part for a while and finally figured it out in Access, but of course that command isn't available in Excel. I need it to figure specifically on month so that the report can be run at any time within a month and still get the same results.

    Also my date format is 07/21/2006 10:49

    When I enter in your function and make the appropriate chagnes i get a num! error. here is a copy of the function:
    =SUMPRODUCT(--(TEXT($G:$G,"mmmm")="july"),$K:$K)/SUMPRODUCT(--(TEXT($G:$G,"mmmm")="july"))
    G is the resolve date column
    K is the time to resolve column
    Last edited by dredd2525; 10-10-2006 at 05:32 PM.

  5. #5
    Registered User
    Join Date
    10-10-2006
    Posts
    14
    woot, granted some momentary inteligence. thank you to gorgette for the formula I got to work.

    =SUMIF($I:$I,MONTH(NOW())-3,K:K)/COUNTIF($I:$I,MONTH(NOW())-3)

  6. #6
    Forum Contributor kraljb's Avatar
    Join Date
    05-26-2004
    Location
    Illinois
    MS-Off Ver
    2007 (recent change)
    Posts
    256
    That is odd that it is returning the num error. I tried it on mine, with making both parts text, and each individually to see if that would duplicate the problem, I cannot get it to duplicate...

    However, to clarify your first question...

    You want to look at the averages for a year ago, and 11 months ago? Then it requires a little more work because you also have to take into affect the year and not just the month....

  7. #7
    Registered User
    Join Date
    10-10-2006
    Posts
    14
    ok, completely cosmetic. but this still comes back to my 2nd problem. How do I get the label for the data to change?
    =MONTH(NOW()) gives me the number of the month. but not the name. is there a better way?

    When I tried to change the format it just says January.

  8. #8
    Registered User
    Join Date
    10-10-2006
    Posts
    14
    Quote Originally Posted by kraljb
    That is odd that it is returning the num error. I tried it on mine, with making both parts text, and each individually to see if that would duplicate the problem, I cannot get it to duplicate...

    However, to clarify your first question...

    You want to look at the averages for a year ago, and 11 months ago? Then it requires a little more work because you also have to take into affect the year and not just the month....
    yeah, the averages from up to a year ago. the table will always only have up to 12 months of data. so up to october of last year.

  9. #9
    Forum Contributor kraljb's Avatar
    Join Date
    05-26-2004
    Location
    Illinois
    MS-Off Ver
    2007 (recent change)
    Posts
    256
    =TEXT(MONTH(NOW()),"mmmm") will return the string "October"

    Glad to see you got things working...

  10. #10
    Registered User
    Join Date
    10-10-2006
    Posts
    14
    maybe it is just me.
    I pasted and manually typed that string and got "January"
    My computer cant tell time. that is re-assuring.

  11. #11
    Registered User
    Join Date
    10-10-2006
    Posts
    14
    ok, figured out a really "ghetto" way of doing it. but if anyone has a better way I'm all ears.
    =IF(MONTH(NOW())-3=7,"July",IF(MONTH(NOW())-3=8, "August")) so on and so forth for all 12 months.

  12. #12
    Forum Contributor kraljb's Avatar
    Join Date
    05-26-2004
    Location
    Illinois
    MS-Off Ver
    2007 (recent change)
    Posts
    256
    The reason for my formula not working is my fault... Try the following...

    =TEXT(DATE(YEAR(NOW()),MONTH(NOW())-3,DAY(NOW())),"mmmm")

    Anyway, with the way you're doing it you'd be better off with a list somewhere else, because you can only nest so many IF statements before Excel desides it doesn't want to do anything...

    On Sheet2
    A,B
    -2, October
    -1, November
    0, December
    1, January
    2, Febuary
    3, March
    4, April
    5, May

    etc.

    Then just do a VLOOKUP(MONTH(NOW())-3,Sheet2!$A$1:$B$12,2,FALSE)

    (The -2, -1, and 0 are for the fact that MONTH(NOW()) will return 1 for January and -3 will be -2)

  13. #13
    Registered User
    Join Date
    10-10-2006
    Posts
    14
    cool, I'll give that a try, yeah I ran into the nested if limit at July. lame.

    with this method wouldn't the corisponding month change depending on what time of year you are running the report?
    Last edited by dredd2525; 10-10-2006 at 06:44 PM.

  14. #14
    Registered User
    Join Date
    02-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Can you do a average with criteria from another column?

    Quote Originally Posted by dredd2525 View Post
    woot, granted some momentary inteligence. thank you to gorgette for the formula I got to work.

    =SUMIF($I:$I,MONTH(NOW())-3,K:K)/COUNTIF($I:$I,MONTH(NOW())-3)
    perfect and simple solution, thank you!!

+ 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