Hi! What formula would I use to calculate an n/a value. For example,
A1-A7 have varying numerical values, but 8 and 9 are n/a. What formula would
I use?

Thanks in advance for your help.

In my test, having the text "N/A" didn't bother a sum of a column including
that text. If you mean, the error value #N/A as the result of a formula, I
usually suppress them from appearing on the sheet with IF(ISERROR(some
calculation),"",some calculation) for that cell. You can also use ISNA if you
specifically want to trap them. ISERROR gets all of the formula errors. It
doubles the size of your formula, but it does the job. If the formula would
have produced an error, you can make it display whatever you want, ""
(blank), 0, "N/A", etc.

Roy

A1 =5
A2 =1
A3 =6
A4 =4
A5 =8
A6 =9
A7 =2
A8 =N/A (a text value)
A9 =N/A (a text value)
A10= 35 (=SUM(A1:A9) This works fine

A1 =5
A2 =1
A3 =6
A4 =4
A5 =8
A6 =9
A7 =2
A8 =IF(ISERROR(Your Formula Here),"",Your Formula Here)
A9 =IF(ISERROR(Your Formula Here),"",Your Formula Here)
A10= 35 '=(SUM(A1:A9)

--
(delete .nospam)




"summerelli" wrote:

Hi! What formula would I use to calculate an n/a value. For example,
A1-A7 have varying numerical values, but 8 and 9 are n/a. What formula would
I use?

Thanks in advance for your help.

If you really have #n/a! error values in some of your cells and you have to keep
those error values, you can use this formula:

=SUM(IF(ISNUMBER(A1:A7),A1:A7))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

(But I like using a formula like Roy Wagner suggested to hide the error.)

summerelli wrote:

Hi! What formula would I use to calculate an n/a value. For example,
A1-A7 have varying numerical values, but 8 and 9 are n/a. What formula would
I use?

Thanks in advance for your help.

--

Dave Peterson