How to get rid of #VALUE! ?

[Phoenix]

Hi!

I got the price in C2:C100 and Pieces in D2100. The Formula in E:E10
is C2*D2, C3*D3 etc. But in som of the cells in C2:C100 i have to ad
some text instead of numbers. This, off course, gives the formula i
column E "#VALUE!"

How can i get rid of "#VALUE!" here, and instead have "0" or just "-
in the formulas in column

 

[Frank Kabel]

Hi
try
=IF(ISNUMBER(C2)*(ISNUMBER(D2),C2*D2,"-")

 

[Rusty_nl]

Including an ISERROR in your calculation should do the trick

=IF(ISERROR(C2*D2);0;C2*D2)

Where the 0 can be "" or something else as well

 

[Phoenix]

I didn't quite tell the hole story.
I got this formula:

D12*E12*IF(ISNA(MATCH("x";F12:I12;0));1;INDEX(F6:I6;MATCH("x";F12:I12;0)))

It tells that if i mark an "x" in F12:I12(just one at the same time)
let's say "x" in F6. Then it should be D12*E12*F6. If not, then jus
D12*E12

The problem is that if I write some text in D12, the formula return
#VALUE!. In the few of may cases i need text there, i need the formul
to be blank or zero or something.

Is it just to replace the "ISNA" with "ISERROR"??


Phoeni

 

[Phoenix]

I ment to say:

"It tells that if i mark an "x" in F12:I12(just one at the same time)
let's say "x" in *F12*. Then it should be D12*E12*F6. If not, then jus
D12*E12"




Phoeni

 

[Frank Kabel]

Hi
try
=IF(ISNUMBER(D12),D12*E12*IF(ISNA(MATCH("x";F12:I12;0));1;INDEX(F6:I6;M
ATCH("x";F12:I12;0))),"")

 

[JE McGimpsey]

One problem is that using the * operator with text will always give your
#VALUE!, regardless of the rest of your formula.

Try:

=IF(ISNUMBER(D12),D12*..., "")

 

[Rusty_nl]

I don't think your formula is doing what you want it to do. It works i
you put in "x" in F12, but if you put in any of the other cells in th
range (G12,H12 or I12) it doesnt work.

And you cant just replace the ISNA with the ISERROR as you need to d
the ISERROR check at the beginning of the formula.

Let me try something

 

[Aladin Akyurek]

=N(C2)*N(D2)