I'm having problems using the SUBSTITUTE function as part of a formula. On it's own, SUBSTITUTE returns the desired output (i.e. in cell O3), but when combined in a formula, 0 is outputted (i.e. cell P3).

Screenshot of the problem

For informatation, the formula in cell O3 is =IF(ISNUMBER(N3), N3-SUBSTITUTE(M3, "+", ""), "")

  • Thanks @pnuts - I've just tried to recreate the cells from scratch and I'm still having the same problem... Is yours working with data exactly as in the screenshot? Oct 1 '14 at 22:00
  • 1
    Good spot, and it definitely works now. I think it's more so silly me for not realising that it would round-down... Oct 1 '14 at 22:11

I think you are getting the correct answer but .2857 is being rounded down to less than £1! (ie it is just the formatting).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.