#N/A In Max min Average Formula

G

Guest

I have lots of tables of data e.g. A1:A12 that is linked to line graphs and
max/min/average calculations.

The graphs need to plot a zero value when zero is entered but plot nothing
when no data is entered.

I am entering #N/A into the table, this solves the graph problem but
interfears with the max/min/average calculations, they now desplay #N/A.

Is there a way to exclude #N/A from the calculations
 
N

NHarkawat

Use
=MAX(IF(ISNA(A1:A300)=FALSE,A1:A300))
and array enter it (use ctrl+shift+enter)
Same for Min and average
 
P

Peo Sjoblom

=MAX(IF(ISNUMBER(A1:A12),A1:A12))

entered with ctrl + shift & enter

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trying to find additional ranges outside of min, max, and average 2
Statistical functions on non-consequtive columns 7
Average formula 3
MAX() and MIN() 4
how do i average the 3 max numbers in a column? 1
Average exluding max and min values 11
Conditional formula using MIN function doesn't work 5
AVERAGE excluding #N/A 3

Top