VBA Express Forum Calculate only the given range.

04-16-2012, 02:04 AM   #1
defcon_3

Joined: Jan 2012
Posts: 68
Kb Entries: 0
Articles: 0
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 To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

Local Time: 10:04 PM
Local Date: 05-18-2013
Location:

 04-16-2012, 07:03 AM #2 xld   Distinguished Lord of VBAX VBAX Expert   Joined: Apr 2005 Posts: 23,118 Kb Entries: 3 Articles: 2 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 Local Time: 08:04 AMLocal Date: 05-19-2013 Location:
 04-16-2012, 08:33 AM #3 p45cal VBAX Regular   Joined: Oct 2005 Posts: 1,698 Kb Entries: 0 Articles: 0 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 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 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. Local Time: 07:04 AMLocal Date: 05-19-2013
 04-16-2012, 06:50 PM #4 defcon_3   VBAX Regular   Joined: Jan 2012 Posts: 68 Kb Entries: 0 Articles: 0 Thanks xld and pascal that works great. I will test on different scenario, and post back result. Thanks.. Local Time: 10:04 PMLocal Date: 05-18-2013 Location:

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Announcements     Announcements Articles     Articles VBA Code & Other Help     How to Get Help     Non English Help     Access Help     Excel Help         SUMPRODUCT And Other Array Functions     Outlook Help     PowerPoint Help     Word Help     Office 2007 Ribbon UI     PowerPivot, PowerView and DataMining     SQL     Integration/Automation of Office Applications Help     MS Project     Other Applications Help     Misc Help     Project Assistance     Testing Area Mac Users     Mac VBA Help     Other Mac Issues Learning VBA     Book Reviews     Resources Member Soap Box     Announcements     Dear Babydum     firefytr's forum     Introductions! (powered by Joseph)

All times are GMT -7. The time now is 12:04 AM.

 -- VBAX Original Green ---- Autumn Gold ---- Cool Blue ---- Flame Red ---- Charcoal (by Joseph) Webmail - Contact Us - Terms - Top