Results 1 to 5 of 5

Thread: Selecting array that equals or is less than value

  1. #1
    Seeker gjohal's Avatar
    Join Date
    Nov 2018
    Posts
    15
    Articles
    0
    Excel Version
    2016

    Selecting array that equals or is less than value



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have data (see attached excel for better detail Q1.xlsx) that has 2 years of monthly data.

    I want to average all the values that equals and is less than a certain month (the value under Month Chosen) for the current year and after the certain month for the past year. Is there any way to do this without adding another row with index numbers for the months? I don't want to do it manually and rather by having a formula that does for me based on the value under Month Chosen.
    Last edited by gjohal; 2018-11-29 at 05:11 PM.

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,675
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Hi and welcome
    as in everyday's life, it's usual to show some courtesy, even on free forums. "Hello" and "thank you" are not considered coarse language.

    It is also expected that you acknowledge the answers received ( be they satisfactory or not), thing you have not done in the first thread you created on this forum.

    Thank you
    Thank you Ken for this secure forum.

  3. #3
    Seeker gjohal's Avatar
    Join Date
    Nov 2018
    Posts
    15
    Articles
    0
    Excel Version
    2016
    Oh sorry about that! Will do from now on, unfortunately can't edit post now to say hello but will do next time. Thanks!

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Try:

    =AVERAGE($B$2:INDEX($B$2:$M$2,MATCH($P$2,$B$1:$M$1,0)),INDEX($B$4:$M$4,MATCH($P$2,$B$1:$M$1,0)+1):$M$4)


  5. #5
    Seeker gjohal's Avatar
    Join Date
    Nov 2018
    Posts
    15
    Articles
    0
    Excel Version
    2016
    Yes! That works perfect, thanks!

Posting Permissions

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