EXCEL QUESTION - intermediate/advanced I suspect 1

[rossmcl]

Or perhaps it is easy and I am just thick.

Is there any way to have a field with a Text Value, ie NA but if another field uses it in a calculation, it takes it as a 0?

eg

B C D(=B*C)
0 1 0
1 2 2
3 6 18
NA 2 0
2 2 4

I cant do it with hidden columns unfortunately - there are too many other cells that rely on that particular reference. Is there anything that could make a forumla derive a value of zero if it sees an NA.

Oh yes, I CANT USE CODE TO FIND/REPLACE all NA's with 0, as there are other 0's which much be distinguishable from the NA's.

Thanks a lot as always.
Ross

[sig][/sig]

 

[dsi]

You could use an IF to dictate the calculation.

For example, cell D5 can contain the formula;

=IF(B5="NA",0,IF(C5="NA",0,B5*C5))

Then, if NA is in column B or C, the value is set to zero.
[sig][/sig]

 

[rossmcl]

No can do. There are too many columns that have this NA, would be to many IF's.

Any other ideas.

Thanks
Ross [sig][/sig]

 

[kathryn]

What about a generic function, call it NAZero()

Function NAZero(c as cell) as integer

if Range(c).value = "NA"
then NAZero = 0
else
NAZero = Range(c).value
end if
end function

I haven't coded in Excel in awhile, so the above will have to be modified.

Then whenever you are going to refer to a cell in a calculation, use the NAZero function instead.

Does this sound feasible? [sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[dsi]

Try testing the mathimatical function using the ISERR function.

Ex. Column E is the product of columns A-D

=IF(ISERR(A4*B4*C4*D4)=FALSE,A4*B4*C4*D4,0)

[sig][/sig]

 

[BobCrawf]

Try a modification of tclere's formula using iserr():

=IF(ISERR(B4*C4),0,B4*C4) [sig][/sig]

 

[rossmcl]

Unfortunately all these solutions all rely on being allowed to use if statements (with the exception of Kathryns).

I cant use ifs because there are too many columns where the NA appears, would involve too too many nested ifs.

What I want is a way of saying, if NA appears, treat it as a 0, but show it on the spreadsheet as a NA.

I had a feeling this was impossible, your suggestions are indicating this is indeed the case.

Kathryn, I will give your solution a test to see if it is feasible,
All, thanks for taking the time to post, as always it is much appreciated.

Regards
Ross
[sig][/sig]

 

[BobCrawf]

Can you give us a real case formula you are using? What you indicated in your original message would not need any nested if statements.

A complex formula can also me modified to take N/A into account without nested If Statements. We just need to know what you are trying to do. [sig][/sig]

 

[rossmcl]

Hi All.

Problem Solved. In its most simple terms I am going to use the below (thanks Kathryn, this was spawned from your idea)

Function NAZero(A, B) As Integer

If A = &quot;NA&quot; Then
NAZero = 0 * B
ElseIf B = &quot;NA&quot; Then
NAZero = A * 0
Else
NAZero = A * B
End If

End Function


THANKS FOR ALL YOUR HELP AND SUGGESTIONS. IT IS MUCH APPRECIATED AS ALWAYS.

Ross [sig][/sig]