Calculate only the given range.

[defcon_3]

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.

[xld]

Try

=AVERAGE(INDEX(H4:S4,MATCH($E4,H$3:S$3,0)):INDEX(H4:S4,MATCH($F4,H$3:S$3,0) ))

[p45cal]

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)

[defcon_3]

Thanks xld and pascal that works great. I will test on different scenario, and post back result. Thanks..