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
 
The asterisks are not there, it somehow gets placed in the table when using Excel Jeanie.
That only happens if you preview or edit your post. In either case the Excel jeanie code is most likely still on your clipboard so just select the old code and paste over the top of it again & you shouldn't get the asterisks. :)
 
Upvote 0

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Taking into account that IF is faster that multiplication:
(this could be fractions)
=MAX(IF(YEAR(A7:A36)<=H10,IF(YEAR(A7:A36)>=H10-2,C7:C36)))
Confirm Control+Shift+Enter
 
Upvote 0
Peter, thanks for letting me know about the Excel Jeanie behavior. It would be nice if it did not do that in the first place, as I always preview my posts before finalizing. I certainly can try to re-paste from clipboard on final edits and submissions in the future. Thanks for that tip.

Rick, I went back and reviewed all the formulas provided by everyone and realized that your first suggested formula worked as it was not array entered. The only one that did not work was Robert's first suggestion in post #2 as his data setup had each row incremented in years while mine was by months.

I did some light testing of the suggested formulas in posts #3 through #12 with my table setup. For example, I moved maximums at different dates, deleting dates but keeping data, and entering text instead of numbers. The following resulted:

If text is entered in just one of the cells in column D instead of numbers, Rick's formulas go to #VALUE! while Dave and Robert's formulas appear to ignore the text and provide the correct maximum. Removal of dates where there is no corresponding maximum value has no affect, while removing the date with a corresponding max forces the next highest value and date to appear as it should.

And for sake of robustness, I forced an error to appear in one cell within the range such as #DIV0! or #NAME? which resulted in none of the formulas to work and simply echoed the error.

While the IF(ISERROR(formula),"",(formula)) would work around that, I generally don't like using that approach as the formula no longer stays compact. I know that Excel 2007 and above use IFERROR, but I still have Excel 2003.

In the past and for other occasions, I use a formula like this:

={SUM(IF(ISNUMBER(C27:E27),C27:E27))} works well as it ignores all errors even #DIV0! and I do not need to do the IF(ISERROR(formula),"",(formula)) approach. I wish that I had a non-array version of that, but it works so well. I think some Excel functions handle data differently (and better) than others.

Thanks again to all for chiming in with your great suggestions and appropriate solutions - it makes learning Excel much more fun.:)
 
Upvote 0
....

While the IF(ISERROR(formula),"",(formula)) would work around that, I generally don't like using that approach as the formula no longer stays compact. I know that Excel 2007 and above use IFERROR, but I still have Excel 2003.

In the past and for other occasions, I use a formula like this:

={SUM(IF(ISNUMBER(C27:E27),C27:E27))} works well as it ignores all errors even #DIV0! and I do not need to do the IF(ISERROR(formula),"",(formula)) approach. I wish that I had a non-array version of that, but it works so well. I think some Excel functions handle data differently (and better) than others.
...

A bit off topic...

Better to have:

=SUM(SUMIF(C27:E27,{"<0",">0"}))

=SUMIF(C27:E27,"<"&9.99E+307)

instead of

{=SUM(IF(ISNUMBER(C27:E27),C27:E27))}
 
Upvote 0
Thank you Aladin for sharing that bit of information. :)

When I tested =SUM(SUMIF(C27:E27,{"<0",">0"})) it does seem to work for negative numbers. I tried modifying to =SUM(SUMIF(E10:G10,"<>0")) but that also does not allow negative numbers as the result seems to be blank.

However, the SUMIF formula works great for all situations.

I appreciate you stopping by.
 
Upvote 0
Thank you Aladin for sharing that bit of information. :)

When I tested =SUM(SUMIF(C27:E27,{"<0",">0"})) it does seem to work for negative numbers. I tried modifying to =SUM(SUMIF(E10:G10,"<>0")) but that also does not allow negative numbers as the result seems to be blank.

However, the SUMIF formula works great for all situations.

I appreciate you stopping by.

=SUM(SUMIF(E10:G10,{"<0",">0"}))

should also work for bot neg and pos numbers, as designed.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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