A Cleaner Way to Find Max Value?

dwgnome

Active Member
Joined
Dec 18, 2005
Messages
441
I am hoping to simplify the following formula that works as intended:

Code:
=MAX(INDEX((YEAR($B$7:$B$40)=$H$4)*$D$7:$D$40,0),INDEX((YEAR($B$7:$B$40)=$H$4-1)*$D$7:$D$40,0),(INDEX((YEAR($B$7:$B$40)=$H$4-2)*$D$7:$D$40,0)))

where H4 houses the number representing the year. The formula then finds the maximum value for the selected year and the rows that have the previous two years. So for example if H4 = 2013, then the formula seeks the maximum value in 2013, 2012 and 2011.

I tried =MAX(INDEX((YEAR($B$7:$B$40)=$H$4:$H$4-2)*$D$7:$D$40,0)), but that only got the maximum in the year 2011.

Thanks.

Excel 2003
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
I am hoping to simplify the following formula that works as intended:

Code:
=MAX(INDEX((YEAR($B$7:$B$40)=$H$4)*$D$7:$D$40,0),INDEX((YEAR($B$7:$B$40)=$H$4-1)*$D$7:$D$40,0),(INDEX((YEAR($B$7:$B$40)=$H$4-2)*$D$7:$D$40,0)))

where H4 houses the number representing the year. The formula then finds the maximum value for the selected year and the rows that have the previous two years. So for example if H4 = 2013, then the formula seeks the maximum value in 2013, 2012 and 2011.

I tried =MAX(INDEX((YEAR($B$7:$B$40)=$H$4:$H$4-2)*$D$7:$D$40,0)), but that only got the maximum in the year 2011.

Thanks.

Excel 2003

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;">2013</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/1/2000</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">111</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/1/2001</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/1/2002</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/1/2003</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/1/2004</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/1/2005</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/1/2006</td><td style="text-align: right;;"></td><td style="text-align: right;;">34</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/1/2007</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/1/2008</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/1/2009</td><td style="text-align: right;;"></td><td style="text-align: right;;">43</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/1/2010</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/1/2011</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;">111</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/1/2012</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/1/2013</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;">43</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/1/2014</td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/1/2015</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/1/2016</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F7</th><td style="text-align:left">{=MAX(<font color="Blue">OFFSET(<font color="Red">B7,MATCH(<font color="Green">E6,YEAR(<font color="Purple">$B$7:$B$40</font>),0</font>)-3,2,3</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,982
Office Version
  1. 2016
Platform
  1. Windows
Here is a different array-entered** formula for you to try...

=MAX(D7:D40*(YEAR(B7:B40)<=H4)*(YEAR(B7:B40)>=H4-2))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

NOTE: I assumed that your data could be laid out slightly differently than Robert did, so make sure you test both formulas out carefully against your actual data. Also note when testing that Robert placed the year in cell E6 instead of cell H4 as your original post indicated, so change his formula accordingly.
 
Last edited:

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,679
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Excel Workbook
BCDEFGH
1Dates______AmountsMax 3 years
2Jan 01, 2005500
3Jan 01, 2005400StartEndYear
4Oct 01, 201030001/01/1131/12/132013
5Jan 01, 2011200400
6Dec 01, 2012100400
7Dec 01, 20130
8Jun 01, 2013400
4b
Excel 2003
Cell Formulas
RangeFormula
F4=DATE(YEAR(End)-2,1,1)
H4=YEAR(End)
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Excel Workbook
NameRefers To
End='4b'!$G$4
rT=zList!$B$2:$D$10
Start='4b'!$F$4
Workbook Defined Names


two other alternatives
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,982
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Here is a different array-entered** formula for you to try...

=MAX(D7:D40*(YEAR(B7:B40)<=H4)*(YEAR(B7:B40)>=H4-2))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

NOTE: I assumed that your data could be laid out slightly differently than Robert did, so make sure you test both formulas out carefully against your actual data. Also note when testing that Robert placed the year in cell E6 instead of cell H4 as your original post indicated, so change his formula accordingly.
Here is a shorter array-entered** formula that works identically to the formula I posted above (hence, the note I posted there is still applicable)...

=MAX(D7:D40*(ABS(YEAR(B7:B40)-H4+1)<2))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.
 

dwgnome

Active Member
Joined
Dec 18, 2005
Messages
441
HHV Missing Data - CA (2)

*BCD
7October 2011**
8November 2011143,717143,717
9December 2011134,689134,689
10January 201267,344*
11February 20120*
12March 20120*
13April 20120*
14May 20120*
15June 20120*
16July 20120*
17August 201267,565*
18September 2012135,130135,130
19October 2012134,943134,943
20November 2012130,049130,049
21December 2012133,660133,660
22January 2013130,677130,677
23February 2013131,358131,358
24March 2013132,038132,038
25April 2013132,719132,719
26May 2013133,400133,400
27June 2013127,955127,955
28July 2013128,635128,635
29August 2013129,316129,316
30September 2013129,997129,997
31October 2013130,827130,827
32November 2013131,582131,582
33December 2013132,338132,338
34January 2014**
35February 2014**
36March 2014**

<tbody>
</tbody>


HHV Missing Data - CA (2)

*J
20 * * * * 133,660
=MAX(OFFSET(B7,MATCH(H4,YEAR($B$7:$B$40),0)-3,2,3))

<tbody>
</tbody>


HHV Missing Data - CA (2)

*J
21
* * * * 133,660​
=MAX(D7:D40*(YEAR(B7:B40)<=H4)*(YEAR(B7:B40)>=H4-2))

<tbody>
</tbody>


Thank you Robert and Rick, I tried both formulas and it did not work as I don't think it is looking at all of the dates within 2013 and the two years prior (2012 and 2011); just three rows from what I can tell.

The maximum value selected should be 143,717.:eek:

Excel tables to the web >> Excel Jeanie HTML 4
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,679
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

You could try one or both of the suggestions that I provided.
Since your data starts at row 7, edit the ranges as required.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,982
Office Version
  1. 2016
Platform
  1. Windows
Actually, my formula should be doing that (that was the difference between my formula and Robert's that I was trying to point out).

Two questions that you must answer...

1. You are showing two columns of values (Column C and Column D)... why? There was no mention of Column C in your original post... only Column D, so our formulas only return values for Column D values. Please clarify why you are showing Column C values along with Column D values now.

2. Also, I see you are showing asterisks mixed in with your numerical values... are they really there or did you use them as stand-ins for empty cells? If they are really there, I need to know because they will screw up the numerical calculations I am doing unless I change my formula to account for them.
 
Last edited:

dwgnome

Active Member
Joined
Dec 18, 2005
Messages
441
Thank you Dave, your approach worked well, but I was looking to just use the one date and using a more compact formula.

Rick, your revised solution worked perfectly and such a compact formula!:)

Column C is the "new" data based on a substitution algorithm (based on regulatory procedures for missing data), while column D is the actual data. You are seeing only a portion of the overall scheme in my real spreadsheet. The two year lookback that you helped me on is one of three data substitution methods based on the data capture rate.

The asterisks are not there, it somehow gets placed in the table when using Excel Jeanie.

Thanks everyone again for such great and fast responses, it really helped.:pray:
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,982
Office Version
  1. 2016
Platform
  1. Windows
I am guessing from your last post that you consider your question resolved... and that is good... but I am slightly worried for you. You said my first formula did not work correctly (although you did not say in what way it did not work correctly), but you said my second, shorter formula did work... I do not see how that is possible. Unless I am mistaken (and I am almost positive I am not), both those formulas should produce identical results, so I do not see how one worked and the other failed. What I am worried about is you ran them against different data samples so that the second formula did not get a chance to trip up on whatever made the first formula fail.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,448
Messages
5,642,218
Members
417,262
Latest member
andrewd1

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
Top