Conditional Formula to ignore text fields.

[MattMax]

Dear Excel Ninjas,

I'm looking to write a formula that ignores cells that have "NA" in them.

The exact formula is: =F8/((B8/B12)+(C8/C12)+(D8/D12)+(E8/E12))

The catch is that if any of those cells within the parenthesis have a value of "NA" then that divided pair should = 0.
So if B8 = "NA", B8/B12 = 0, or if B12 = "NA", B8/B12 = 0.

Would prefer to do this with an excel formula rather then a macro. Would appreciate any pointers.

Thanks in advance.
MattMax

 

[MattMax]

I think I might have it with a If(And(ISNUMBER)) function... trying to string them together now.

 

[Snakehips]

MattMax

Assuming that NA is a value in your data rather than a #N/A error then for (B8/B12) substitute .......

IF(ISNUMBER(B8/B12),C8/C12,0)


and similar for the C8/C12 and D8/D12

Hope this helps?

 

[MattMax]

Thanks..

I ended up going with this giant formula that is working nicely (cells are different positions then in my example):

=W7/((IF(AND(ISNUMBER(F7),ISNUMBER(J7)),J7/F7,0)+(IF(AND(ISNUMBER(G7),ISNUMBER(M7)),M7/G7,0))+(IF(AND(ISNUMBER(H7),ISNUMBER(P7)),P7/H7,0))+(IF(AND(ISNUMBER(I7),ISNUMBER(S7)),S7/I7,0))))

 

[Snakehips]

Hey, why use the nutcrackers if you have a sledgehammer handy!!??