A Cleaner Way to Find Max Value?

[dwgnome]

I am hoping to simplify the following formula that works as intended:

=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

 

[Robert Mika]

I am hoping to simplify the following formula that works as intended:

=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
	A	B	C	D	E	F
4
5
6					2013
7		1/1/2000		3		111
8		1/1/2001		5
9		1/1/2002		3
10		1/1/2003		4
11		1/1/2004		5
12		1/1/2005		3
13		1/1/2006		34
14		1/1/2007		4
15		1/1/2008		3
16		1/1/2009		43
17		1/1/2010		3
18		1/1/2011		111
19		1/1/2012		4
20		1/1/2013		43
21		1/1/2014		2
22		1/1/2015		3
23		1/1/2016		3
Sheet1

Cell Formulas
Range	Formula
F7	{=MAX(OFFSET(B7,MATCH(E6,YEAR($B$7:$B$40),0)-3,2,3))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Rick Rothstein]

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.

 

[Dave Patton]

Excel Workbook
	B	C	D	E	F	G	H
1	Dates	______	Amounts				Max 3 years
2	Jan 01, 2005		500
3	Jan 01, 2005		400		Start	End	Year
4	Oct 01, 2010		300		01/01/11	31/12/13	2013
5	Jan 01, 2011		200				400
6	Dec 01, 2012		100				400
7	Dec 01, 2013		0
8	Jun 01, 2013		400
4b

Excel 2003

Cell Formulas
Range	Formula
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
Name	Refers To
End	='4b'!$G$4
rT	=zList!$B$2:$D$10
Start	='4b'!$F$4
Workbook Defined Names

two other alternatives

 

[Rick Rothstein]

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]

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

 

[Dave Patton]

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]

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.

 

[dwgnome]

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:

 

[Rick Rothstein]

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.