NA Problem MAX

[stephen.smith]

HI Guys
I have a problem i have 3 cells e.g. a1, a2, a3 and they all have specific values if filled in by employees. For example they may have a value from 1-10 or if not filled in the have the #NA value. I am looking to find the max value from this array of numbers however when i use the max formula it only works if there is a number in all 3 cells. If one of the cells reads #NA then #NA is returned in the max formula. Can you help me please.
Thanks In Advance
Stephen

 

[MrKowz]

The best way to accomplish this is to eliminate the #N/A errors altogether.

 

[Richard Schollar]

I agree with Keith, but you can use an array formula to filter out the errors:

Excel Workbook
	L
10	5
11	6
12	#N/A
13	6
Sheet2

Excel 2010

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[stephen.smith]

This would be too difficult as I have 40 of these to deal with on a daily basis and would slow down my day significantly.