+ Reply to Thread
Results 1 to 11 of 11

IF Formula returning wrong answer

  1. #1
    Registered User
    Join Date
    07-04-2009
    Location
    Sacramento, CA
    MS-Off Ver
    Excel 2013
    Posts
    14

    IF Formula returning wrong answer

    I am trying to determine if I can write a formula that will read text in one cell, such as insurance and if true enter the amount from another cell and if false then 0. I have tried a IF statement by add the text to name manager. However, the answer is 0 rather than the amount in the selected cell. Can anyone help with this problem. I might need to use a different formula or function. Thank you
    Last edited by Abyrose; 05-01-2014 at 08:56 PM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Formula Help

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: IF Formula returning wrong answer

    I am assuming the title has changed since the moderator's reply is Re: A Different Title to the current one.

    To the OP - to know what the problem is we need more details, an example maybe. Your logic above seems correct, but unless we know what your formula says, how you're checking the text for instance, we can't help fix the issue.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: IF Formula returning wrong answer

    Is something like this what you're trying to do...

    If cell A1 = insurance return the value from cell B1 otherwise return 0

    If that's what you want to do try this formula:

    =IF(A1="Insurance",B1,0)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    07-04-2009
    Location
    Sacramento, CA
    MS-Off Ver
    Excel 2013
    Posts
    14

    Re: IF Formula returning wrong answer

    Thank you for your response. My formula is very similar =IF(D12=Insurance, K12, (0)). I tried your formula IF(D12="Insurance",K12,0) but I still get the false answer of 0. I'm looking for the value in K12.

  6. #6
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: IF Formula returning wrong answer

    Ensure that in the data you are looking in Insurance is in fact "Insurance", not something like "Insurance " (ie. with a space at the end). Try =If(isnumber(find("Insurance",D12)),K12,0).

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: IF Formula returning wrong answer

    OK, I assume you can see what's in cell D12 and it looks like it contains Insurance, correct?

    Maybe there are unseen whitespace characters like:

    [space]Insurance
    Insurance[space]
    [space]Insurance[space]

    Does this work:

    =IF(TRIM(D12)="Insurance",K12,0)

  8. #8
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: IF Formula returning wrong answer

    Hi Tony,

    How would the if condition change, if the cell D12 contains words like "Health Insurance", "Vehicle Insurance", etc. ?

    I assume this might be one of the cases, as the user says the formula is similar to yours :

    Quote Originally Posted by Abyrose View Post
    Thank you for your response. My formula is very similar =IF(D12=Insurance, K12, (0)). I tried your formula IF(D12="Insurance",K12,0) but I still get the false answer of 0. I'm looking for the value in K12.
    Abyrose,

    You say that you are getting the false answer 0, if your formula is similar to Tony's, how do you say that the final result of the if condition is false ?

    Last edited by Saarang84; 05-01-2014 at 09:55 PM.
    If my assistance has helped, there is a reputation icon * on the left hand corner below the post - you can show your appreciation to the user who has helped in resolving your requirement.

    If your requirement has been solved please mark your thread as Solved.
    In the menu bar above the very first post, select Thread Tools, then select "Mark this thread as Solved".

    Kindly use [FORMULA] or [CODE] tags when posting your code.

    Regards,
    Sarang

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: IF Formula returning wrong answer

    A few ways...

    If the word Insurance may be anywhere within the cell:

    =IF(COUNTIF(D12,"*Insurance*"),K12,0)

    =IF(COUNT(SEARCH("Insurance",D12)),K12,0)

    If the word Insurance is always the last word in the cell:

    =IF(RIGHT(D12,9)="Insurance",K12,0)

  10. #10
    Registered User
    Join Date
    07-04-2009
    Location
    Sacramento, CA
    MS-Off Ver
    Excel 2013
    Posts
    14

    Re: IF Formula returning wrong answer

    Quote Originally Posted by gak67 View Post
    Ensure that in the data you are looking in Insurance is in fact "Insurance", not something like "Insurance " (ie. with a space at the end). Try =If(isnumber(find("Insurance",D12)),K12,0).
    Thank you, this formula worked. I also had an extra space following insurance.

  11. #11
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: IF Formula returning wrong answer

    If you find your requirement solved, then you can mark it as Solved against the thread name by choosing from the Thread tools menu which is found between your thread name and the first post you made.

    Also, you can add to the reputation of the experts who have helped you with a solution to your requirement as you may like.

+ 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. how to hide formula in formula box, view lookup result in formula box?
    By vengatvj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2013, 04:06 PM

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