LARGE value if more then one cell have value

[Thomas Martin]

I have this formula that I need help to modify
=LARGE(CHOOSE({1,2,3,4,5},Z3,AC3,AF3,AI3,AL3),1)
I want modify the formula to only function with a result if there more than one value to calculate. If there is only one value the result would be 0 or blank

 

[wideboydixon]

Try it like this:

=IF(SUMPRODUCT(--(CHOOSE({1,2,3,4,5},Z3,AC3,AF3,AI3,AL3)<>0))>1,LARGE(CHOOSE({1,2,3,4,5},Z3,AC3,AF3,AI3,AL3),1),0)

or

=IF(SUMPRODUCT(--(CHOOSE({1,2,3,4,5},Z3,AC3,AF3,AI3,AL3)<>0))>1,LARGE(CHOOSE({1,2,3,4,5},Z3,AC3,AF3,AI3,AL3),1),"")

WBD

 

[Thomas Martin]

Thank you for the response!~ I tried both formulas and they both provided a value even if there was only one cell of the 5 that was populated with a value.
Any additional ideas?

 

[Marcelo Branco]

Maybe...

=IF(COUNT(Z3,AC3,AF3,AI3,AL3)>1,LARGE((Z3,AC3,AF3,AI3,AL3),1),0)

M.

 

[wideboydixon]

What are the contents of the cells that don't have values? Mine are blank here:

Book1
	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
3	5	1			2			3			4			5
4	0				2
5	6	6						3
Sheet1

Cell Formulas
Range	Formula
Y3	=IF(SUMPRODUCT(--(CHOOSE({1,2,3,4,5},Z3,AC3,AF3,AI3,AL3)<>0))>1,LARGE(CHOOSE({1,2,3,4,5},Z3,AC3,AF3,AI3,AL3),1),0)

WBD

 

[Thomas Martin]

Maybe...

=IF(COUNT(Z3,AC3,AF3,AI3,AL3)>1,LARGE((Z3,AC3,AF3,AI3,AL3),1),0)

M.

Thank you! It worked like a charm!
=IF(COUNT(Z2,AC2,AF2,AI2,AL2)>1,LARGE((Z2,AC2,AF2,AI2,AL2),1),"")

 

[Marcelo Branco]

Thank you! It worked like a charm!
=IF(COUNT(Z2,AC2,AF2,AI2,AL2)>1,LARGE((Z2,AC2,AF2,AI2,AL2),1),"")

You are welcome. Glad to help.

M.