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

[YahooGoogle]

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

Days	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34
3	101.5%
4	76.5%	91.0%
5	49.2%	58.6%	110.2%
6	53.8%	64.0%	120.3%	122.4%
7	61.4%	73.0%	137.3%	139.7%	162.0%
8	62.1%	73.9%	139.0%	141.4%	164.0%	113.9%
9	75.8%	90.1%	169.5%	172.4%	200.0%	138.9%	149.3%
10	56.8%	67.6%	127.1%	129.3%	150.0%	104.2%	111.9%	83.3%
11	58.3%	69.4%	130.5%	132.8%	154.0%	106.9%	114.9%	85.6%	110.0%
12	98.5%	117.1%	220.3%	224.1%	260.0%	180.6%	194.0%	144.4%	185.7%	240.7%
13	85.6%	101.8%	191.5%	194.8%	226.0%	156.9%	168.7%	125.6%	161.4%	209.3%	166.2%
14	68.2%	81.1%	152.5%	155.2%	180.0%	125.0%	134.3%	100.0%	128.6%	166.7%	132.4%	100.0%
15	66.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%
16	41.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%
17	38.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%
18	28.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%
19	23.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%
20	18.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%
21	13.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%
22	30.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%
23	61.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%
24	56.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%
25	17.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%
26	18.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%
27	49.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%
28	7.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%
29	3.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%
30	3.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%
31	1.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!
35	0.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!

 

[jasonb75]

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]

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]

@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]

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]

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
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34
2	101.50%
3	76.50%	91.00%
4	49.20%	58.60%	110.20%
5	53.80%	64.00%	120.30%	122.40%
6	61.40%	73.00%	137.30%	139.70%	162.00%
7	62.10%	73.90%	139.00%	141.40%	164.00%	113.90%
8	75.80%	90.10%	169.50%	172.40%	200.00%	138.90%	149.30%
9	56.80%	67.60%	127.10%	129.30%	150.00%	104.20%	111.90%	83.30%
10	58.30%	69.40%	130.50%	132.80%	154.00%	106.90%	114.90%	85.60%	110.00%
11	98.50%	117.10%	220.30%	224.10%	260.00%	180.60%	194.00%	144.40%	185.70%	240.70%
12	85.60%	101.80%	191.50%	194.80%	226.00%	156.90%	168.70%	125.60%	161.40%	209.30%	166.20%
13	68.20%	81.10%	152.50%	155.20%	180.00%	125.00%	134.30%	100.00%	128.60%	166.70%	132.40%	100.00%
14	66.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%
15	41.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%
16	38.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%
17	28.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%
18	23.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%
19	18.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%
20	13.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%
21	30.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%
22	61.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%
23	56.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%
24	17.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%
25	18.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%
26	49.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%
27	7.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%
28	3.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%
29	3.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%
30	1.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!
34	0.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
36	722.20%
Sheet2 (3)

Cell Formulas
Range	Formula
B36	B36	=AGGREGATE(14,6,B2:AH34,1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:AH34	Expression	=B2=AGGREGATE(14,6,$B$2:$AH$34,1)	text	NO

 

[jasonb75]

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]

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]

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.

Book1
	A	B	C	D
36	7.222	26	25	Y26
37	5.063	22	21	U22
38	4.625	23	21	U23
39	4.063	26	21	U26
40	4.063	26	26	Z26
Sheet25

Cell Formulas
Range	Formula
A36:A40	A36	=AGGREGATE(14,6,$B$2:$AH$34,ROWS(A$36:A36))
B36:B40	B36	=IFERROR(AGGREGATE(15,6,ROW($B$2:$AH$34)/($B$2:$AH$34=A36),COUNTIF(A$36:A36,A36)),"")
C36:C40	C36	=IFERROR(AGGREGATE(15,6,COLUMN($B$2:$AH$34)/(INDEX($B:$AH,B36,0)=A36),COUNTIF(A$36:A36,A36)),"")
D36:D40	D36	=ADDRESS(B36,C36,4)

 

[Peter_SSs]

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