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

Good Afternoon

I was wondering if anyone can help me I have to following IF statement but it does not seem to work at all

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

EG if cell C7 and F7 both are zero I want to return a zero, However if cell C7 is equal to or grater than one I want the formula to calculate out

This is not working for some reason, any help would be appreciated

Thanks
CraigieL

Remove the quotes from around the zeros within the AND function.

Hope this helps.

Pete

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

Also (I should really read the full post first !!), I'm not sure why you have F7/F7 in there - you could replace that with 1.

Pete

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

Here you go in row 7 it has the formula as stated and showing 0


Test Doc.xls

Thanks

CraigieL

The formula in H7 is not the same as the one I gave you in post #4 - it is the original formula that you quoted in the first post. Change it and it will return the correct %ages if C7 and F7 are not zero.

Hope this helps.

Pete

Hi Pete

Apologies, in error I must have copied over the previous formula in #3

Now working great and as expected

Thanks again for your help

CraigieL