# A Cleaner Way to Find Max Value?

#### dwgnome

##### Active Member
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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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 2010
ABCDEF
4
5
62013
71/1/20003111
81/1/20015
91/1/20023
101/1/20034
111/1/20045
121/1/20053
131/1/200634
141/1/20074
151/1/20083
161/1/200943
171/1/20103
181/1/2011111
191/1/20124
201/1/201343
211/1/20142
221/1/20153
231/1/20163
Sheet1
Cell Formulas
RangeFormula
F7{=MAX(OFFSET(B7,MATCH(E6,YEAR(\$B\$7:\$B\$40),0)-3,2,3))}
Press CTRL+SHIFT+ENTER to enter array formulas.

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:
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

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.

HHV Missing Data - CA (2)

 * B C D 7 October 2011 * * 8 November 2011 143,717 143,717 9 December 2011 134,689 134,689 10 January 2012 67,344 * 11 February 2012 0 * 12 March 2012 0 * 13 April 2012 0 * 14 May 2012 0 * 15 June 2012 0 * 16 July 2012 0 * 17 August 2012 67,565 * 18 September 2012 135,130 135,130 19 October 2012 134,943 134,943 20 November 2012 130,049 130,049 21 December 2012 133,660 133,660 22 January 2013 130,677 130,677 23 February 2013 131,358 131,358 24 March 2013 132,038 132,038 25 April 2013 132,719 132,719 26 May 2013 133,400 133,400 27 June 2013 127,955 127,955 28 July 2013 128,635 128,635 29 August 2013 129,316 129,316 30 September 2013 129,997 129,997 31 October 2013 130,827 130,827 32 November 2013 131,582 131,582 33 December 2013 132,338 132,338 34 January 2014 * * 35 February 2014 * * 36 March 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.

Excel tables to the web >> Excel Jeanie HTML 4

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

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:
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.ray:

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.

Replies
2
Views
246
Replies
5
Views
113
Replies
2
Views
586
Replies
11
Views
418
Replies
4
Views
138

1,211,871
Messages
6,104,479
Members
447,911
Latest member
rkramapo

### 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.

### Which adblocker are you using?

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

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