Microsoft Excel Webinar

Results 1 to 4 of 4

Thread: Calculate only the given range.

  1. #1

    Calculate only the given range.

    Hi guys,

    Can you give me a hand to calculate only the data on given range. I had this workbook that has a yearly rating on the user and I want to calculate it quarterly based on the input from column E and F which is the start and the end. See attached file for details.
    If it reach Nov - Jan it will look back the value of Jan and so on for Dec - Feb.
    The pattern is a 3 consecutive mos, it will only loop back when it fall to Nov and Dec.

    Thanks you.
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    23,565
    Location
    Try

    =AVERAGE(INDEX(H4:S4,MATCH($E4,H$3:S$3,0)):INDEX(H4:S4,MATCH($F4,H$3:S$3,0) ))
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    My first attempt (ugh!):
    =IF(MONTH(DATEVALUE($E4 & "00")) > MONTH(DATEVALUE($F4 & "00")),AVERAGE(IF((--NOT((COLUMN($A$1:$L$1) < MONTH(DATEVALUE($E4 & "00")))*(COLUMN($A$1:$L$1) > MONTH(DATEVALUE($F4 & "00"))))),$H4:$S4,FALSE)),AVERAGE(IF((COLUMN($A$1:$L$1) > =MONTH(DATEVALUE($E4 & "00")))*(COLUMN($A$1:$L$1)<=MONTH(DATEVALUE($F4 & "00")))=1,$H4:$S4,FALSE)))

    and array-entered to boot.

    Then I saw xld's solution.
    My original solution wraps round, so if the start month is later in the year than the end month (say Nov start and Jan finish) it averages Jan, Nov, Dec.

    I don't think xld's does, so mangling xld's solution:
    =IF(MATCH($E4,H$3:S$3,0) > MATCH($F4,H$3:S$3,0), AVERAGE(INDEX($H4:$S4,MATCH($E4,$H$3:$S$3,0)):$S4,H4:INDEX($H4:$S4,MATCH($F 4,$H$3:$S$3,0))),AVERAGE(INDEX(H4:S4,MATCH($E4,H$3:S$3,0)):INDEX(H4:S4,MATC H($F4,H$3:S$3,0))))

    perhaps xld can make this more elegant?

    such fun! (Miranda)
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    Thanks xld and pascal that works great. I will test on different scenario, and post back result. 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
  •