Hi,

Your problems stem from a couple of misunderstandings. First of all if you check the 'numbers' in columns D & F you'll find they are Text, not Numbers. Use the formula =ISNUMBER(D20) etc. to confirm this.

The fact that they are text doesn't stop Excel being clever enough to recognise that because you are including arithmetic operators you intend them to be numbers, and it treats them as such. However the IF() test is something different.

Note what happens for instance if in F19 you enter "=+1+1.5" instead of just "2.5"

When you use =IF(D19>F19... since D19 & F19 are strings you're comparing the first character of D19, i.e. 7, with the 1st char. of F, i.e. 2. In this case the test is true hence you get 4.5 in N19. Now it just so happens that here D>F and hence you get the 'right' answer of 3. On Row 20, you are still getting the right answer -3 since the 1st char. of D20 is > the 1st char. of F20 (i.e. 1), it's just that you're 'thinking' it's the wrong answer. As far as XL is concerned it is the right answer.

If you format columns D, F, M, N & O to be numbers rather than text, and then re-enter the numbers you'll be OK.

As I mentioned earlier, your IF() formula don't have a third default element, which in some case can lead to odd results. You'd be better changing the formulae in M & N to

If you don't want the zeros to show it would be best to switch them off in the Tools Options View menu. Otherwise if you replace the last zero in the column N formulae above with "" , you'll find that you get errors in col O, and will have to modify that to say

HTH

## Bookmarks