#Value

[Luminary Xion]

Why is this giving me an error? C8 is blank and E8 has a 0 in it.

=IF(SUBSTITUTE(E8,",",".")-SUBSTITUTE(C8,",",".")<>0,SUBSTITUTE(E8,",",".")-SUBSTITUTE(C8,",","."),"0")

Note that E8-C8 normally produces 0, all I'm doing is trying to automagically adapt the euro , into the american .

[shg]

Excel can't calculate"0" - ""

[Luminary Xion]

Then why does it work fine as E8-C8? that's still "0" - "" isn't it?



Note that "=SUBSTITUTE(E8,",",".")-SUBSTITUTE(C8,",",".")" doesn't work either.

[daddylonglegs]

Not sure why you need the IF. Try

=SUBSTITUTE(0&E8,",",".")-SUBSTITUTE(0&C8,",",".")

[Luminary Xion]

Smarty! That fixed it, thanks.

[Luminary Xion]

Okay, how can I do the same thing in the middle of this equation?
=SUM(OtherTab!C3:I3)

I need to substitute to change any , to . on C3:I3 for the math to work. Advice?

[Luminary Xion]

Any clues? Is this possible?

[daddylonglegs]

You can do that in a similar way, try

=SUMPRODUCT(SUBSTITUTE(0&OtherTab!C3:I3,",",".")+0)

[Luminary Xion]

Thanks muchness! That works like a charm.