Roundup Max Value In Range With #N/A

[goss]

Hi all,

Using Excel 2010.

I have a table that summarizes a dataset.
If the dataset is missing a datapoint or if sums to zero, then NA() is returned for charting.

Now I would like to create some custom horizontal lines on the chart so that they may appear in front of the plotted series.

I'm trying =ROUNDUP(MAX(C10:N17),1) to get my uppermost value which may grow/shrink over time. I can then determine additional horizontal lines from that point by subtracting in increments of 0.5 Billion.

However, since there are #N/A within the range, my formula is returning #N/A. LARGE(C10:N17,1) also retruned #N/A.

Is there a way around this?

 

[VoG]

Try this array formula confirmed with CTRL + Shift + Enter, not just Enter

=ROUNDUP(MAX(IF(ISNUMBER(C10:N17),C10:N17)),1)

 

[goss]

Thanks Peter,

Works great!