Ignore NA in a formula when I type it in a cell
 

=IF(F60,10,0)+IF(H6=1,10,0)+IF(I6=1,10,0)+IF(Q6 =0,10,0)+(K6*20)+(M6*20)+(O6*20)-SUM(R6+S6)

When I put a NA into cells K6, M6, O6, R6, S6, it won't calculate obviously
because NA is not a number. It gives me the error message. Is there some
way to bypass this when a cell has a NA?

--
Thanks!

Tyler

Ignore NA in a formula when I type it in a cell
 

Try this:

=((F60)+(H6=1)+(I6=1)+(Q6=0))*10+SUM(K6,M6,O6) *20-SUM(R6:S6)


"Tyler Manhattan" wrote:

=IF(F60,10,0)+IF(H6=1,10,0)+IF(I6=1,10,0)+IF(Q6 =0,10,0)+(K6*20)+(M6*20)+(O6*20)-SUM(R6+S6)

When I put a NA into cells K6, M6, O6, R6, S6, it won't calculate obviously
because NA is not a number. It gives me the error message. Is there some
way to bypass this when a cell has a NA?

--
Thanks!

Tyler

Ignore NA in a formula when I type it in a cell
 

=IF(F60,10,0)+IF(H6=1,10,0)+IF(I6=1,10,0)+IF(Q6 =0,10,0)
+IF(ISNUMBER(K6),K6,0)*20+IF(ISNUMBER(M6),M6,0)*20 +IF(ISNUMBER(O6),O6,0)*20
-(IF(ISNUMBER(R6),R6,0)+IF(ISNUMBER(S6),S6,0))

--
__________________________________
HTH

Bob

"Tyler Manhattan" wrote in
message ...
=IF(F60,10,0)+IF(H6=1,10,0)+IF(I6=1,10,0)+IF(Q6 =0,10,0)+(K6*20)+(M6*20)+(O6*20)-SUM(R6+S6)

When I put a NA into cells K6, M6, O6, R6, S6, it won't calculate
obviously
because NA is not a number. It gives me the error message. Is there some
way to bypass this when a cell has a NA?

--
Thanks!

Tyler

Ignore NA in a formula when I type it in a cell
 

Instead of K6*20 use something like SUM(K6)*20 or MIN(K6)*20. Looks silly
but this prevents the error message when K6 contains text like 'NA':

=IF(F60,10,0)+IF(H6=1,10,0)+IF(I6=1,10,0)+IF(Q6 =0,10,0)+(MIN(K6)*20)+(MIN(M6)*20)+(MIN(O6)*20)-SUM(MIN(R6)+MIN(S6))

Cheers,

Joerg Mochikun


"Bob Phillips" wrote in message
...
=IF(F60,10,0)+IF(H6=1,10,0)+IF(I6=1,10,0)+IF(Q6 =0,10,0)
+IF(ISNUMBER(K6),K6,0)*20+IF(ISNUMBER(M6),M6,0)*20 +IF(ISNUMBER(O6),O6,0)*20
-(IF(ISNUMBER(R6),R6,0)+IF(ISNUMBER(S6),S6,0))

--
__________________________________
HTH

Bob

"Tyler Manhattan" wrote in
message ...
=IF(F60,10,0)+IF(H6=1,10,0)+IF(I6=1,10,0)+IF(Q6 =0,10,0)+(K6*20)+(M6*20)+(O6*20)-SUM(R6+S6)

When I put a NA into cells K6, M6, O6, R6, S6, it won't calculate
obviously
because NA is not a number. It gives me the error message. Is there
some
way to bypass this when a cell has a NA?

--
Thanks!

Tyler

Ignore NA in a formula when I type it in a cell
 

Sorry, that was too fast. Better:
Instead of K6*20 use N(K6)*20.

The function N converts non-number values to numbers.

Joerg Mochikun

"Joerg Mochikun" wrote in message
...
Instead of K6*20 use something like SUM(K6)*20 or MIN(K6)*20. Looks silly
but this prevents the error message when K6 contains text like 'NA':

=IF(F60,10,0)+IF(H6=1,10,0)+IF(I6=1,10,0)+IF(Q6 =0,10,0)+(MIN(K6)*20)+(MIN(M6)*20)+(MIN(O6)*20)-SUM(MIN(R6)+MIN(S6))

Cheers,

Joerg Mochikun


"Bob Phillips" wrote in message
...
=IF(F60,10,0)+IF(H6=1,10,0)+IF(I6=1,10,0)+IF(Q6 =0,10,0)
+IF(ISNUMBER(K6),K6,0)*20+IF(ISNUMBER(M6),M6,0)*20 +IF(ISNUMBER(O6),O6,0)*20
-(IF(ISNUMBER(R6),R6,0)+IF(ISNUMBER(S6),S6,0))

--
__________________________________
HTH

Bob

"Tyler Manhattan" wrote in
message ...
=IF(F60,10,0)+IF(H6=1,10,0)+IF(I6=1,10,0)+IF(Q6 =0,10,0)+(K6*20)+(M6*20)+(O6*20)-SUM(R6+S6)

When I put a NA into cells K6, M6, O6, R6, S6, it won't calculate
obviously
because NA is not a number. It gives me the error message. Is there
some
way to bypass this when a cell has a NA?

--
Thanks!

Tyler