How to find max value with empty cells & #div/0!

YahooGoogle

New Member
Joined
Nov 7, 2017
Messages
14
Office Version
  1. 2007
Hello, I have inlcuded an example chart that I use regularly for work below (assuming it starts in cell A1 - so data range would be B2:AH34). Is there a way to find the max value of this chart, or even the top 5 max values (would be ideal but will settle for just one max figure for now if it's not possible)? I have tried numerous things but it seems that the empty cells and/or the #DIV/0! cells are causing issues for me. Also, is there a way to have a separate cell next to the max number that references the actual cell that this max value is found (to save me time from having to look through each cell individually until I find that max number the formula spits out)?

Thank you in advance


Days2345678910111213141516171819202122232425262728293031323334
3101.5%
476.5%91.0%
549.2%58.6%110.2%
653.8%64.0%120.3%122.4%
761.4%73.0%137.3%139.7%162.0%
862.1%73.9%139.0%141.4%164.0%113.9%
975.8%90.1%169.5%172.4%200.0%138.9%149.3%
1056.8%67.6%127.1%129.3%150.0%104.2%111.9%83.3%
1158.3%69.4%130.5%132.8%154.0%106.9%114.9%85.6%110.0%
1298.5%117.1%220.3%224.1%260.0%180.6%194.0%144.4%185.7%240.7%
1385.6%101.8%191.5%194.8%226.0%156.9%168.7%125.6%161.4%209.3%166.2%
1468.2%81.1%152.5%155.2%180.0%125.0%134.3%100.0%128.6%166.7%132.4%100.0%
1566.7%79.3%149.2%151.7%176.0%122.2%131.3%97.8%125.7%163.0%129.4%97.8%123.9%
1641.7%49.5%93.2%94.8%110.0%76.4%82.1%61.1%78.6%101.9%80.9%61.1%77.5%84.6%
1738.6%45.9%86.4%87.9%102.0%70.8%76.1%56.7%72.9%94.4%75.0%56.7%71.8%78.5%102.0%
1828.0%33.3%62.7%63.8%74.0%51.4%55.2%41.1%52.9%68.5%54.4%41.1%52.1%56.9%74.0%92.5%
1923.5%27.9%52.5%53.4%62.0%43.1%46.3%34.4%44.3%57.4%45.6%34.4%43.7%47.7%62.0%77.5%103.3%
2018.9%22.5%42.4%43.1%50.0%34.7%37.3%27.8%35.7%46.3%36.8%27.8%35.2%38.5%50.0%62.5%83.3%104.2%
2113.6%16.2%30.5%31.0%36.0%25.0%26.9%20.0%25.7%33.3%26.5%20.0%25.4%27.7%36.0%45.0%60.0%75.0%72.0%
2230.3%36.0%67.8%69.0%80.0%55.6%59.7%44.4%57.1%74.1%58.8%44.4%56.3%61.5%80.0%100.0%133.3%166.7%160.0%250.0%
2361.4%73.0%137.3%139.7%162.0%112.5%120.9%90.0%115.7%150.0%119.1%90.0%114.1%124.6%162.0%202.5%270.0%337.5%324.0%506.3%289.3%
2456.1%66.7%125.4%127.6%148.0%102.8%110.4%82.2%105.7%137.0%108.8%82.2%104.2%113.8%148.0%185.0%246.7%308.3%296.0%462.5%264.3%137.0%
2517.4%20.7%39.0%39.7%46.0%31.9%34.3%25.6%32.9%42.6%33.8%25.6%32.4%35.4%46.0%57.5%76.7%95.8%92.0%143.8%82.1%42.6%38.3%
2618.9%22.5%42.4%43.1%50.0%34.7%37.3%27.8%35.7%46.3%36.8%27.8%35.2%38.5%50.0%62.5%83.3%104.2%100.0%156.3%89.3%46.3%41.7%277.8%
2749.2%58.6%110.2%112.1%130.0%90.3%97.0%72.2%92.9%120.4%95.6%72.2%91.5%100.0%130.0%162.5%216.7%270.8%260.0%406.3%232.1%120.4%108.3%722.2%406.3%
287.6%9.0%16.9%17.2%20.0%13.9%14.9%11.1%14.3%18.5%14.7%11.1%14.1%15.4%20.0%25.0%33.3%41.7%40.0%62.5%35.7%18.5%16.7%111.1%62.5%45.5%
293.8%4.5%8.5%8.6%10.0%6.9%7.5%5.6%7.1%9.3%7.4%5.6%7.0%7.7%10.0%12.5%16.7%20.8%20.0%31.3%17.9%9.3%8.3%55.6%31.3%22.7%71.4%
303.0%3.6%6.8%6.9%8.0%5.6%6.0%4.4%5.7%7.4%5.9%4.4%5.6%6.2%8.0%10.0%13.3%16.7%16.0%25.0%14.3%7.4%6.7%44.4%25.0%18.2%57.1%400.0%
311.5%1.8%3.4%3.4%4.0%2.8%3.0%2.2%2.9%3.7%2.9%2.2%2.8%3.1%4.0%5.0%6.7%8.3%8.0%12.5%7.1%3.7%3.3%22.2%12.5%9.1%28.6%200.0%50.0%
32#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
33#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
34#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
350.8%0.9%1.7%1.7%2.0%1.4%1.5%1.1%1.4%1.9%1.5%1.1%1.4%1.5%2.0%2.5%3.3%4.2%4.0%6.3%3.6%1.9%1.7%11.1%6.3%4.5%14.3%100.0%25.0%100.0%#DIV/0!#DIV/0!#DIV/0!
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,974
Office Version
  1. 2019
Platform
  1. Windows
Try

=AGGREGATE(14,6,B2:AH34,1)

The 1 at the end denotes the largest (max) value, changing it to 2 will give you the second largest value and so on.

Note that if the largest value is repeated then the results will repeat accordingly rather than showing the largest 5 unique values.
 

Lee J

New Member
Joined
Jun 30, 2020
Messages
43
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You could you then conditional format your data to show a colour or bold text if the values match the aggregate cell values
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,786
Office Version
  1. 365
Platform
  1. Windows
@YahooGoogle
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 

KBARAD

New Member
Joined
Mar 31, 2020
Messages
9
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

I seem to remember this is a common thing to solve with array formulae - particularly since you can do an IFERROR to replace the error values with another value (e.g. -1 which would filter itself out of any max function if you are always in the 0+% range elsewhere). Trying to find where the max is in a 2d range is trickier without using VBA - I don't know of a 2d match function
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,786
Office Version
  1. 365
Platform
  1. Windows
In what way do the suggestions made above fail?
In cell B36 below is the formula from post #2 and the suggestion from post #3 results in cell Y26 being highlighted.

In terms of referencing the maximum value, what do you expect to happen if, say, four cells all contain the equal maximum value?

20 08 25.xlsm
BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
12345678910111213141516171819202122232425262728293031323334
2101.50%
376.50%91.00%
449.20%58.60%110.20%
553.80%64.00%120.30%122.40%
661.40%73.00%137.30%139.70%162.00%
762.10%73.90%139.00%141.40%164.00%113.90%
875.80%90.10%169.50%172.40%200.00%138.90%149.30%
956.80%67.60%127.10%129.30%150.00%104.20%111.90%83.30%
1058.30%69.40%130.50%132.80%154.00%106.90%114.90%85.60%110.00%
1198.50%117.10%220.30%224.10%260.00%180.60%194.00%144.40%185.70%240.70%
1285.60%101.80%191.50%194.80%226.00%156.90%168.70%125.60%161.40%209.30%166.20%
1368.20%81.10%152.50%155.20%180.00%125.00%134.30%100.00%128.60%166.70%132.40%100.00%
1466.70%79.30%149.20%151.70%176.00%122.20%131.30%97.80%125.70%163.00%129.40%97.80%123.90%
1541.70%49.50%93.20%94.80%110.00%76.40%82.10%61.10%78.60%101.90%80.90%61.10%77.50%84.60%
1638.60%45.90%86.40%87.90%102.00%70.80%76.10%56.70%72.90%94.40%75.00%56.70%71.80%78.50%102.00%
1728.00%33.30%62.70%63.80%74.00%51.40%55.20%41.10%52.90%68.50%54.40%41.10%52.10%56.90%74.00%92.50%
1823.50%27.90%52.50%53.40%62.00%43.10%46.30%34.40%44.30%57.40%45.60%34.40%43.70%47.70%62.00%77.50%103.30%
1918.90%22.50%42.40%43.10%50.00%34.70%37.30%27.80%35.70%46.30%36.80%27.80%35.20%38.50%50.00%62.50%83.30%104.20%
2013.60%16.20%30.50%31.00%36.00%25.00%26.90%20.00%25.70%33.30%26.50%20.00%25.40%27.70%36.00%45.00%60.00%75.00%72.00%
2130.30%36.00%67.80%69.00%80.00%55.60%59.70%44.40%57.10%74.10%58.80%44.40%56.30%61.50%80.00%100.00%133.30%166.70%160.00%250.00%
2261.40%73.00%137.30%139.70%162.00%112.50%120.90%90.00%115.70%150.00%119.10%90.00%114.10%124.60%162.00%202.50%270.00%337.50%324.00%506.30%289.30%
2356.10%66.70%125.40%127.60%148.00%102.80%110.40%82.20%105.70%137.00%108.80%82.20%104.20%113.80%148.00%185.00%246.70%308.30%296.00%462.50%264.30%137.00%
2417.40%20.70%39.00%39.70%46.00%31.90%34.30%25.60%32.90%42.60%33.80%25.60%32.40%35.40%46.00%57.50%76.70%95.80%92.00%143.80%82.10%42.60%38.30%
2518.90%22.50%42.40%43.10%50.00%34.70%37.30%27.80%35.70%46.30%36.80%27.80%35.20%38.50%50.00%62.50%83.30%104.20%100.00%156.30%89.30%46.30%41.70%277.80%
2649.20%58.60%110.20%112.10%130.00%90.30%97.00%72.20%92.90%120.40%95.60%72.20%91.50%100.00%130.00%162.50%216.70%270.80%260.00%406.30%232.10%120.40%108.30%722.20%406.30%
277.60%9.00%16.90%17.20%20.00%13.90%14.90%11.10%14.30%18.50%14.70%11.10%14.10%15.40%20.00%25.00%33.30%41.70%40.00%62.50%35.70%18.50%16.70%111.10%62.50%45.50%
283.80%4.50%8.50%8.60%10.00%6.90%7.50%5.60%7.10%9.30%7.40%5.60%7.00%7.70%10.00%12.50%16.70%20.80%20.00%31.30%17.90%9.30%8.30%55.60%31.30%22.70%71.40%
293.00%3.60%6.80%6.90%8.00%5.60%6.00%4.40%5.70%7.40%5.90%4.40%5.60%6.20%8.00%10.00%13.30%16.70%16.00%25.00%14.30%7.40%6.70%44.40%25.00%18.20%57.10%400.00%
301.50%1.80%3.40%3.40%4.00%2.80%3.00%2.20%2.90%3.70%2.90%2.20%2.80%3.10%4.00%5.00%6.70%8.30%8.00%12.50%7.10%3.70%3.30%22.20%12.50%9.10%28.60%200.00%50.00%
31#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
32#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
33#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
340.80%0.90%1.70%1.70%2.00%1.40%1.50%1.10%1.40%1.90%1.50%1.10%1.40%1.50%2.00%2.50%3.30%4.20%4.00%6.30%3.60%1.90%1.70%11.10%6.30%4.50%14.30%100.00%25.00%100.00%#DIV/0!#DIV/0!#DIV/0!
35
36722.20%
Sheet2 (3)
Cell Formulas
RangeFormula
B36B36=AGGREGATE(14,6,B2:AH34,1)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:AH34Expression=B2=AGGREGATE(14,6,$B$2:$AH$34,1)textNO
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,974
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

In what way do the suggestions made above fail?
I don't see where anyone said that they have failed, Peter. The OP hasn't responded yet.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,786
Office Version
  1. 365
Platform
  1. Windows
I don't see where anyone said that they have failed, Peter. The OP hasn't responded yet
I didn't make it clear. Response was to KBARAD who seemed to be indicating that an array formula was required (implying that the suggestion so far was inadequate) & that it was tricky to locate the max value. I was suggesting that both things had already been done, :)
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,974
Office Version
  1. 2019
Platform
  1. Windows
Also, is there a way to have a separate cell next to the max number that references the actual cell that this max value is found
I think that this was the part that @KBARAD was referring to, Peter. Whilst the suggestion of conditionally formatting the table to match the results is a good and far more practical alternative, it is not what the OP asked for.

This is a variation of a method that I've used a couple of times to find row and column headers, it appears to work but I expect that there is room for improvement.
Cell Formulas
RangeFormula
A36:A40A36=AGGREGATE(14,6,$B$2:$AH$34,ROWS(A$36:A36))
B36:B40B36=IFERROR(AGGREGATE(15,6,ROW($B$2:$AH$34)/($B$2:$AH$34=A36),COUNTIF(A$36:A36,A36)),"")
C36:C40C36=IFERROR(AGGREGATE(15,6,COLUMN($B$2:$AH$34)/(INDEX($B:$AH,B36,0)=A36),COUNTIF(A$36:A36,A36)),"")
D36:D40D36=ADDRESS(B36,C36,4)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,786
Office Version
  1. 365
Platform
  1. Windows
Whilst the suggestion of conditionally formatting the table to match the results is a good and far more practical alternative, it is not what the OP asked for.
Which is why I asked
In terms of referencing the maximum value, what do you expect to happen if, say, four cells all contain the equal maximum value?
OP might just want any ref, might want all refs in separate cells, might want all refs 'textjoined' in a single cell, might want a warning message, ...
 

Watch MrExcel Video

Forum statistics

Threads
1,112,997
Messages
5,543,190
Members
410,584
Latest member
Bluefox68
Top