find/match datesand apply formula?

G

Guillemot

New Member
Joined
Jan 18, 2009
Messages
2
Hi All,

I am trying to locate all the cells withing a given date range and apply a function them. The data look like this:

<table x:str="" style="border-collapse: collapse; width: 247pt;" border="0" cellpadding="0" cellspacing="0" width="330"><col style="width: 48pt;" span="2" width="64"> <col style="width: 53pt;" width="71"> <col style="width: 50pt;" width="67"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" height="17" width="64">site</td> <td style="width: 48pt;" width="64">date</td> <td style="width: 53pt;" width="71">TMIN</td> <td style="width: 50pt;" width="67">TMAX</td> <td style="width: 48pt;" width="64">GDAYS</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">F</td> <td class="xl24">1/1/2004</td> <td class="xl25" x:num="-1.1111111111111112" align="right">-1.11</td> <td class="xl25" x:num="10.555555555555555" align="right">10.56</td> <td class="xl25" x:num="4.7222222222222223" align="right">4.72</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">F</td> <td class="xl24">1/2/2004</td> <td class="xl25" x:num="2.7777777777777777" align="right">2.78</td> <td class="xl25" x:num="18.333333333333336" align="right">18.33</td> <td class="xl25" x:num="10.555555555555557" align="right">10.56</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">F</td> <td class="xl24">1/3/2004</td> <td class="xl25" x:num="-1.1111111111111112" align="right">-1.11</td> <td class="xl25" x:num="11.111111111111111" align="right">11.11</td> <td class="xl25" x:num="" align="right">5.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">F</td> <td class="xl24">1/4/2004</td> <td class="xl25" x:num="-4.4444444444444446" align="right">-4.44</td> <td class="xl25" x:num="-0.55555555555555558" align="right">-0.56</td> <td class="xl25" x:num="" align="right">0.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">F</td> <td class="xl24">1/5/2004</td> <td class="xl25" x:num="-16.666666666666668" align="right">-16.67</td> <td class="xl25" x:num="-4.4444444444444446" align="right">-4.44</td> <td class="xl25" x:num="" align="right">0.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">F</td> <td class="xl24">1/6/2004</td> <td class="xl25" x:num="-18.888888888888889" align="right">-18.89</td> <td class="xl25" x:num="-7.7777777777777786" align="right">-7.78</td> <td class="xl25" x:num="" align="right">0.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">F</td> <td class="xl24">1/7/2004</td> <td class="xl25" x:num="" align="right">-15.00</td> <td class="xl25" x:num="-2.2222222222222223" align="right">-2.22</td> <td class="xl25" x:num="" align="right">0.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">F</td> <td class="xl24">1/8/2004</td> <td class="xl25" x:num="-4.4444444444444446" align="right">-4.44</td> <td class="xl25" x:num="1.6666666666666667" align="right">1.67</td> <td class="xl25" x:num="" align="right">0.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">F</td> <td class="xl24">1/9/2004</td> <td class="xl25" x:num="-6.1111111111111116" align="right">-6.11</td> <td class="xl25" x:num="-2.2222222222222223" align="right">-2.22</td> <td class="xl25" x:num="" align="right">0.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">F</td> <td class="xl24">1/10/2004</td> <td class="xl25" x:num="" align="right">-10.00</td> <td class="xl25" x:num="-1.6666666666666667" align="right">-1.67</td> <td class="xl25" x:num="" align="right">0.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">F</td> <td class="xl24">1/11/2004</td> <td class="xl25" x:num="-3.8888888888888893" align="right">-3.89</td> <td class="xl25" x:num="8.3333333333333339" align="right">8.33</td> <td class="xl25" x:num="2.2222222222222223" align="right">2.22</td> </tr> </tbody></table>

On a separate worksheet, I have the start and end dates that I want to run the calculations on. For example, I'd like to calculate the sum of the GDAYS column and the average of the TMAX column between the start and end date.

<table x:str="" style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" height="36" width="348"><col style="width: 77pt;" width="102"> <col style="width: 71pt;" width="95"> <col style="width: 65pt;" width="87"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 77pt;" height="17" width="102">Start</td> <td style="width: 71pt;" width="95">End</td> <td style="width: 65pt;" width="87">Sum_GDAYS</td> <td style="width: 48pt;" width="64">Mean_TMAX</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">1/2/2004</td> <td class="xl24" x:num="37994" align="right">1/8/2004</td> <td>
</td> <td>
</td> </tr> </tbody></table>
Is there a formula I can use to easily locate the data within the date range and run these calculations?
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
One solution:

G4 = Start Date
H4 = End Date

Sum all GDAYS

=SUMPRODUCT(--($B$2:$B$12>=G4),--(B2:B12<=H4),E2:E12)


Average TMAX

=SUMPRODUCT(--($B$2:$B$12>=G4),--($B$2:$B$12<=H4),$D$2:$D$12)/SUMPRODUCT(--($B$2:$B$12>=G4),--($B$2:$B$12<=H4),--($B$2:$B$12<>""))
 
Upvote 0
[1] Conditional sum of GDAYS...

C2, Sheet2...

Control+shift+enter, not just enter:

=SUM(IF(Sheet1!$B$2:$B$12>=A2,IF(Sheet1!$B$2:$B$12<=B2,Sheet1!$E$2:$E$12)))

Note. An equivalent formula with SumProduct will also do.

[2] Conditional average of TMAX...

Control+shift+enter, not just enter:

=AVERAGE(IF(Sheet1!$B$2:$B$12>=A2,IF(Sheet1!$B$2:$B$12<=B2,Sheet1!$D$2:$D$12)))
 
Upvote 0
Excellent! Thanks to both of you.

I tried both proposed solutions, and both work. Not that it makes a big difference in this application, but is one set of calculations more resource intensive than the other?
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
158
dilshod_k
D
C
  • Solved
Index match function
Replies
3
Views
455
Alex Blakenburg
A
B
  • Solved
Sorting Formula Generated Dates in a Table Excel 2013
2
Replies
12
Views
528
Peter_SSs
Peter_SSs
F
  • Question
Enhance simply subtract of date/times of time spent, to including indexing and subtracting time blocked from another table (with multiple criteria)
Replies
0
Views
225
freeb1893
F
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,214,990
Messages
6,122,625
Members
449,093
Latest member
catterz66

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top