IF statement to return 0 if 0 in cell if not return formula

Originally Posted by Pete_UK

Actually, you also need to remove the second AND, and you can get rid of some of the brackets, like this:

Formula:

IF(AND(C7=0,F7=0),0,IF(C7>=1,(F7-C7)/C7,F7/F7))

Hope this helps.

Pete

Hi Pete

Yes that helps and returns a zero value if the cells are zero, but does not bring back correct calc value if populated with numbers.

EG if C7 was 19 and F7 was 18 the calc would return -5.26% but using the formula above it returns 100%

Any Ideas ?

Thanks
CraigieL

So, is C7 a numeric value or a text value that looks like a number? Put =ISTEXT(C7) in an empty cell somewhere.

Pete

C7 and F7 are numeric Pete

Hope this helps

Thanks
CraigieL

Well you can see that the TRUE action for the second IF statement is:

(F7-C7)/C7

which should give you -5.26%, so there is something about the comparison

C7>=1

that is giving you the FALSE action.

Are you sure you have typed it correctly?

Pete

Hi Pete

Yes using the formula as processed

IF(AND(C7=0,F7=0),0,IF(C7>=1,(F7-C7)/C7,F7/F7))

But works if cells are 0 but not with values

Thanks
CraigieL

Well, all I can suggest is that you post the workbook here, so I can see what is going wrong.

Pete