PDA

View Full Version : Calculate only the given range.



defcon_3
04-16-2012, 02:04 AM
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.

Bob Phillips
04-16-2012, 07:03 AM
Try

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

p45cal
04-16-2012, 08:33 AM
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
04-16-2012, 06:50 PM
Thanks xld and pascal that works great. I will test on different scenario, and post back result. Thanks..