+ Reply to Thread
Results 1 to 14 of 14

IF Function Returns Wrong Answer

  1. #1
    Forum Contributor
    Join Date
    12-16-2014
    Location
    Riverside, IA
    MS-Off Ver
    2010
    Posts
    156

    IF Function Returns Wrong Answer

    Hello All,

    =IF(C8="Lion",'Data Input'!B58*E15,0)

    C8 is a drop down list with 12 choices

    E15 = 2,005

    'Data Input'!B58 = 60%

    Correct answer = 1,203

    My answer = 0

    Thank you,

    Patrick

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,386

    Re: IF Function Returns Wrong Answer

    First thing to check is the second bit of the formula. What does this bit return on its own?

    'Data Input'!B58*E15
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: IF Function Returns Wrong Answer

    Hi EverClever,

    I'd bet either E15 or B58 is TEXT and not a number. If it is text then the value is equal to zero. See if that is the problem.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,386

    Re: IF Function Returns Wrong Answer

    Further to Marvin's post, you can then try this:

    VALUE('Data Input'!B58)*VALUE(E15)

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: IF Function Returns Wrong Answer

    Or the other possiblity is C8 in fact does NOT = Lion

    Is this TRUE or FALSE
    =C8="Lion"

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: IF Function Returns Wrong Answer

    Hey Jonmo,

    Good guess

    Because he said it was a dropdown, I assumed he wouldn't have a space after Lion or spelled it wrong, but you might be correct.

  7. #7
    Forum Contributor
    Join Date
    12-16-2014
    Location
    Riverside, IA
    MS-Off Ver
    2010
    Posts
    156

    Re: IF Function Returns Wrong Answer

    AliGW,

    Just using the E15*'Data Input'!*B58 = #VALUE!

    Your suggested formula returns the same error VALUE('Data Input'!B58)*VALUE(E15)= #VALUE!

    MarvinP,

    E15 or B58 is not TEXT

    Jonmo1,

    The dropdown list did have Lion selected

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: IF Function Returns Wrong Answer

    rather than playing guessing games...

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  9. #9
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: IF Function Returns Wrong Answer

    The dropdown list did have Lion selected
    That wasn't really what I was suggesting.

    Maybe the Lion in your dropdown is actually " Lion" or "Lion ", with a leading or trailing space.
    Which is why I suggested this to test for sure.
    =C8="Lion"

  10. #10
    Forum Contributor
    Join Date
    12-16-2014
    Location
    Riverside, IA
    MS-Off Ver
    2010
    Posts
    156

    Re: IF Function Returns Wrong Answer

    I uploaded the sample worksheet
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    12-16-2014
    Location
    Riverside, IA
    MS-Off Ver
    2010
    Posts
    156

    Re: IF Function Returns Wrong Answer

    Lion is indented 5 spaces so I changed to " Lion" and got a #VALUE! error

  12. #12
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: IF Function Returns Wrong Answer

    Hi EverClever,

    Because there are so many millions of people who use Excel, I'd bet you are doing something different that what Excel Expects. I'm still believing that one of your two cells is Text and not a number. I've seen this many times. For example, have your tried an ISNUMBER function on your two values? Is the name of your other worksheet correct? Look for something that you are doing wrong and not that the IF function doesn't work. Change some things around and see what happens.
    Last edited by MarvinP; 07-28-2017 at 02:25 PM.

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: IF Function Returns Wrong Answer

    1. Cahnge to "Lion" NO spaces and change the formula to

    =IF(C9="Lion",E15*'Grading Scale'!B58,0)

  14. #14
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: IF Function Returns Wrong Answer

    ok, even with that corrected (C8 indeed does NOT = Lion)
    I correct that in the book, and still get 0 because B58 is blank.
    Blank is considered 0 so
    E15*0 = 0

+ 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] match function lookup array returns wrong value
    By elenama in forum Excel General
    Replies: 5
    Last Post: 08-17-2015, 06:13 AM
  2. VBA Vlookup function returns wrong values
    By alicelia in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-29-2015, 12:09 AM
  3. [SOLVED] MAX(date1;date2) returns wrong answer
    By cristinik in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-07-2013, 07:33 AM
  4. right answer has value 1,wrong answer has value 0
    By zeroist in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-10-2012, 10:45 AM
  5. Row() function returns wrong row and more..
    By Kim in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-10-2006, 09:14 PM
  6. My Datedif function only returns 0's in the cell what's wrong?
    By Tom in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-13-2006, 02:45 PM
  7. month worksheet function returns wrong value
    By Barry in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-27-2006, 08:37 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