|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
August 26th, 2008, 09:38 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 
|
|
#2
August 26th, 2008, 10:07 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
|
|
#3
August 26th, 2008, 10:17 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
|
|
#4
August 27th, 2008, 09:50 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
|
|
#5
August 27th, 2008, 10:23 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
