+ Reply to Thread
Results 1 to 14 of 14

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

  1. #1
    Registered User
    Join Date
    10-18-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    19

    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

    Please Login or Register  to view this content.

    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

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,896

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

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

    Hope this helps.

    Pete

  3. #3
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

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

    Perhaps.

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

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,896

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

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

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Hope this helps.

    Pete

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,896

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

    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

  6. #6
    Registered User
    Join Date
    10-18-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    19

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

    Quote Originally Posted by Pete_UK View Post
    Actually, you also need to remove the second AND, and you can get rid of some of the brackets, like this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    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

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,896

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

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

    Pete

  8. #8
    Registered User
    Join Date
    10-18-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    19

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

    C7 and F7 are numeric Pete

    Hope this helps

    Thanks
    CraigieL

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,896

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

    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

  10. #10
    Registered User
    Join Date
    10-18-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    19

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

    Hi Pete

    Yes using the formula as processed

    Please Login or Register  to view this content.
    But works if cells are 0 but not with values

    Thanks
    CraigieL

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,896

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

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

    Pete

  12. #12
    Registered User
    Join Date
    10-18-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    19

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

    Hi Pete

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


    Test Doc.xls

    Thanks

    CraigieL

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,896

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

    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

  14. #14
    Registered User
    Join Date
    10-18-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    19

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

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. statement / formula to return text from a range of cells
    By nellyc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-30-2013, 07:55 AM
  2. Replies: 20
    Last Post: 04-07-2013, 09:36 PM
  3. If Formula looking at a number of cells to return a statement
    By tylerjam1980 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-22-2012, 11:54 AM
  4. IF statement to return value from another cell.
    By Ghornet54 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-01-2010, 11:32 AM
  5. If statement to return nothing if cell is blank
    By amusn in forum Excel General
    Replies: 5
    Last Post: 10-19-2007, 02:25 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1