Date domains and selecting linked cells too compute average

patrick81 · Sep 13, 2011

Hi all,

My name is Patrick and I have a not so difficult question, though too difficult for me

I want to select the dates between for example A7 (20-apr-90) and 30 days before A7.
All these dates (which can differ in amount because of the irregular date intervals, have to be linked to their respective B cells (which is Price of Porsche).
So, if for example A 10 (6-mei-90) falls in the 30 days interval, B 10 has to be selected.Then, all the prices which are linked to the 30 days interval have to be calculated for an average price.
For example: In the 30 days interval for A7 the following dates are included: A7, A6 and A5: (20-apr-90, 6-apr-90 and 22-mrt-90).
So I want to calculate the average of their respective B cells: (B7, B6 and B5: 45000, 56000 and 33000).

<table style="width: 248px; height: 417px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:2523;width:52pt" width="69"> <col style="mso-width-source:userset;mso-width-alt:3913;width:80pt" width="107"> <col style="mso-width-source:userset;mso-width-alt:6070;width:125pt" width="166"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:52pt" height="20" width="69">Date</td> <td style="width:80pt" width="107">Price of Porsche</td> <td style="width:125pt" width="166">Average return in 30 days</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20" align="right">15-feb-90</td> <td align="right">50000</td> <td>
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20" align="right">23-feb-90</td> <td align="right">45000</td> <td>
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20" align="right">27-feb-90</td> <td align="right">46000</td> <td>
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">22-mrt-90</td> <td align="right">33000</td> <td>
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20" align="right">6-apr-90</td> <td align="right">56000</td> <td>
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20" align="right">20-apr-90</td> <td align="right">45000</td> <td>
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20" align="right">27-apr-90</td> <td align="right">60000</td> <td>
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20" align="right">4-mei-90</td> <td align="right">62000</td> <td>
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20" align="right">6-mei-90</td> <td align="right">57000</td> <td>
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20" align="right">18-mei-90</td> <td align="right">45000</td> <td>
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20" align="right">18-mei-90</td> <td align="right">67000</td> <td>
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20" align="right">23-mei-90</td> <td align="right">70000</td> <td>
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20" align="right">24-mei-90</td> <td align="right">56000</td> <td>
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20" align="right">3-jun-90</td> <td align="right">67000</td> <td>
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20" align="right">21-jun-90</td> <td align="right">90000</td> <td>
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20" align="right">25-jun-90</td> <td align="right">74000</td> <td>
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20" align="right">29-jun-90</td> <td align="right">45000</td> <td>
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20" align="right">29-jun-90</td> <td align="right">60000</td> <td>
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20" align="right">19-jul-90</td> <td align="right">34000</td> <td>
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20" align="right">19-jul-90</td> <td align="right">56000</td> <td>
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20" align="right">19-jul-90</td> <td align="right">67000</td> <td>
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20" align="right">19-jul-90</td> <td align="right">80000</td> <td>
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20" align="right">20-jul-90</td> <td align="right">68000</td> <td>
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> </tbody></table>

Andrew Poulsom · Sep 13, 2011

Welcome to MrExcel.

In C2 type:

=AVERAGE(IF(A$2:A$24<=A2,IF(A$2:A$24>(A2-30),B$2:B$24)))

and press Ctrl+Shift+Enter. If correctly entered Excel will surround the formula with curly braces {}. Copy the formula down.

patrick81 · Sep 13, 2011

Andrew Poulsom said:
Welcome to MrExcel.

In C2 type:

=AVERAGE(IF(A$2:A$24<=A2,IF(A$2:A$24>(A2-30),B$2:B$24)))

and press Ctrl+Shift+Enter. If correctly entered Excel will surround the formula with curly braces {}. Copy the formula down.

Hello Andrew, thanks for your quick reply.

When I paste the formula into fx and press Ctrl+Shift+Enter or Enter alone I get an error message. Am I doing something wrong?

Andrew Poulsom · Sep 13, 2011

Maybe you need to replace the commas in the formula with semicolons:

=AVERAGE(IF(A$2:A$24<=A2;IF(A$2:A$24>(A2-30);B$2:B$24)))

patrick81 · Sep 13, 2011

Andrew Poulsom said:
Maybe you need to replace the commas in the formula with semicolons:

=AVERAGE(IF(A$2:A$24<=A2;IF(A$2:A$24>(A2-30);B$2:B$24)))

Yes, this one works perfectly, many thanks!

Date domains and selecting linked cells too compute average

patrick81

New Member

Excel Facts

Andrew Poulsom

MrExcel MVP

patrick81

New Member

Andrew Poulsom

MrExcel MVP

patrick81

New Member

Similar threads

Forum statistics

Share this page

Date domains and selecting linked cells too compute average

patrick81

New Member

Excel Facts

Andrew Poulsom

MrExcel MVP

patrick81

New Member

Andrew Poulsom

MrExcel MVP

patrick81

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock