Please help with Excel

[ejnallstars]

Is it possible to format a cell so that it can't accept negative figures?

I can set it up so the sum of this cell is one cell minus another but I do not want a negative answer only for the answer to go down to zero.

e.g. 5-10= -5 but I would like it to go only as low as zero.

This may seem strange but it is to make a spreadsheet for my tax return and an allowance will be higher than tax due but will only reduce the figure to zero .

I hope that makes sense.

My version is Excel 2000.

[Prew]

You could probably write an If, Then formula to do this.

[Cirerrek]

=IF(AND(ISNUMBER(C10),C10>0),C10,"")

This basically says that IF C10 is really a number AND C10 is greater than 0 THEN display C10, OTHERWISE display nothing.

Cirerrek

[ejnallstars]

Thanks Cirerrek

This formula works perfectly. I could get the figure to dispaly 0 if in the negative but not the actual figure if in the positive.

It does work but I can`t say that I really understand it.

Thanks again.

[doctorgonzo]

The IF function works like this:

IF(condition, what to do if condition is true, what to do if condition is false)

It evaluates the condition, and if it is true it returns the middle argument in the list. If it is false, it returns the last argument in the list. In your example, if C10 is a number and is positive (that's the condition), it displays C10 (the middle argument). Otherwise, it displays a blank (those quotes that make up the last argument in the list).

[Cirerrek]

If you want it also to display 0 then just change the formula to look like this:

=IF(AND(ISNUMBER(C10),C10>=0),C10,"")

which says

IF C10 is really a number AND C10 is greater than or equal to 0 THEN display C10, OTHERWISE display nothing.

IF(logical_test,[value_if_true],[value_if_false])

the [] don't really need to be there

AND(logical1,[logical2],...)

The AND just lets you pass more than one logical statement, in this case 1) Is it a number? and 2) Is it greater than or equal to 0?

Cirerrek

[ejnallstars]

Thanks Guys

This forum never lets me down.

Now I can work out my tax return any help with paying it?

[Cirerrek]

Uhm, considering taxes were due a while ago, I'm going to have to say no help here (thinks interest payments alone might be nasty), but for next year you might want to modify your W2

[David_Jones]

A simpler solution, without the error checking to see if you have actually entered a number, would be:

=max(0, A1 - A2)

Where A1 and A2 contain your original 5, and 10 (say).

This will evaluate to zero with those numbers, but if A2 if greater than A1, then it will give the difference.

HTH,

David.

[ejnallstars]

Thanks David

That works also and seems an easier formula.