Results 1 to 6 of 6

Thread: Average IF / ONLY formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Average IF / ONLY formula

    Afternoon all,

    Scoured the forum and can't find anything that meets my needs.

    I'm looking to create a "library" of sorts that calculates an average score, based on a number of marking definitions.

    We often look back through previous months and update scores so I was trying to find out if there was a foruma that would calculate an average only if the date put in collumn B correlated with a specific month.

    Looking around online I've tried a reccomended formula "{=AVERAGE(IF(MONTH(B:B=5),H:H))}" but it doesn't seem to have worked.

    http://tinypic.com/r/24eudxl/9

    http://tinypic.com/r/21dndc9/9

    Thanks in advance

  2. #2
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,651
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Average IF / ONLY formula

    Are you trying to do averages of averages?

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average IF / ONLY formula

    Quote Originally Posted by steve the fish View Post
    Are you trying to do averages of averages?
    Hi Steve,

    I am yes. Is this possible?

    Kind regards,
    Matthew

  4. #4
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,651
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Average IF / ONLY formula

    It is but doesnt yield the correct average very often. You should almost always use the full data.

  5. #5
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,651
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Average IF / ONLY formula

    Heres an example:

    Length Number
    6 10
    6 10
    4 1

    The average length using all the data is approx 5.9. Your method will produce 5.33.
    Last edited by steve the fish; Jul 24th, 2019 at 09:00 AM.

  6. #6
    Rules violation
    Join Date
    Jan 2012
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average IF / ONLY formula

    formula syntax is wrong

    Code:
    =AVERAGE(IF(MONTH(B:B)=5,H:H))

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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