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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
537
smozgur
smozgur
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
349
dilshod_k
D
C
  • Solved
Index match function
Replies
3
Views
719
Alex Blakenburg
A
B
  • Solved
Sorting Formula Generated Dates in a Table Excel 2013
2
Replies
12
Views
901
Peter_SSs
Peter_SSs
F
  • Question 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
388
freeb1893
F

Forum statistics

Threads
1,223,246
Messages
6,170,996
Members
452,373
Latest member
TimReeks

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