+ Reply to Thread
Results 1 to 8 of 8

Troubleshooting the 'if' function

  1. #1
    Registered User
    Join Date
    07-28-2014
    Location
    canberra, australia
    MS-Off Ver
    2013
    Posts
    5

    Exclamation Troubleshooting the 'if' function

    im having trouble using the IF function. Basically i want to be able to create a cell that multiples several other cells as well as ADDS another. So far the formula works but if i do not enter any information into the other cells it shows a value of '1' which is really annoying. How can i edit my formula to avoid the '1'?

    This is the formula i'm using:

    IF(G5="",1,G5) * IF(H5="",1,H5) +I5 *IF(G5&H5="",0,1)

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,583

    Re: Troubleshooting the 'if' function

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

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

    Kaper

  3. #3
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Troubleshooting the 'if' function

    I would remove the 1's from the formula

    IF(G5="",1,G5) * IF(H5="",1,H5) +I5 *IF(G5&H5="",0,1)

    Basically if you leave a cell blank, the formula is saying 1*1, which is 1.

    Try
    IF(G5="",0,G5) * IF(H5="",0,H5) +I5 *IF(G5&H5="",0,1)

    Or just
    =G5*H5+I5?
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  4. #4
    Registered User
    Join Date
    07-28-2014
    Location
    canberra, australia
    MS-Off Ver
    2013
    Posts
    5

    Re: Troubleshooting the 'if' function

    @kaper

    Thanks kaper, your second formula works but only if i enter values into all 3 working cells. Is there any way of it calculating regardless of weather i fill all the cells?

    =IF(OR(E17="",G17="",H17=""),"",E17*G17*H17+I17)

    @Speshul

    Thanks i tried that formula but it doesn't seem to work. I don't know why but when i copy the cell format to the cells directly below it it returns a different value. Also G5*H5+I5 doesn't seem to work either :S thanks tho

  5. #5
    Registered User
    Join Date
    07-28-2014
    Location
    canberra, australia
    MS-Off Ver
    2013
    Posts
    5

    Re: Troubleshooting the 'if' function

    @kaper

    I forgot to mention i added another multiplication cell to your formula!

  6. #6
    Registered User
    Join Date
    07-28-2014
    Location
    canberra, australia
    MS-Off Ver
    2013
    Posts
    5

    Re: Troubleshooting the 'if' function

    Thanks kaper, your second formula works but only if i enter values into all 3 working cells. Is there any way of it calculating regardless of weather i fill all the cells?

    =IF(OR(E17="",G17="",H17=""),"",E17*G17*H17+I17)

  7. #7
    Registered User
    Join Date
    07-28-2014
    Location
    canberra, australia
    MS-Off Ver
    2013
    Posts
    5

    Re: Troubleshooting the 'if' function

    Quote Originally Posted by Kaper View Post
    how about:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    or:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Thanks kaper, your second formula works but only if i enter values into all 3 working cells. Is there any way of it calculating regardless of weather i fill all the cells?

    =IF(OR(E17="",G17="",H17=""),"",E17*G17*H17+I17)

  8. #8
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,583

    Re: Troubleshooting the 'if' function

    But ... what shall be the output if (for instance) G5 or H5 is empty?
    soemthing multiplied by empty (could be treated as 0) so ... shall return 0? - use just:
    Please Login or Register  to view this content.
    missing values in multiplication treated as 1?
    Please Login or Register  to view this content.

+ 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. [SOLVED] Troubleshooting a countif :-( Seriously
    By Scalpel4 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-01-2014, 11:39 AM
  2. Need help troubleshooting userform
    By dragonabsurdum in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-30-2013, 03:03 PM
  3. [SOLVED] IF statement troubleshooting
    By MARKSTRO in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-14-2012, 08:30 PM
  4. Troubleshooting the worksheet?
    By fatmermaid in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-08-2011, 10:24 AM
  5. New to Sumproduct, need troubleshooting
    By DavidPhillips in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-22-2005, 11:53 AM

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