Consulting

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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    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
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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
    Everyone: 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
  •