+ Reply to Thread
Results 1 to 8 of 8

IF(OR function keeps giving #VALUE! result

  1. #1
    Registered User
    Join Date
    09-07-2012
    Location
    Australia
    MS-Off Ver
    Excel 16.38
    Posts
    18

    Question IF(OR function keeps giving #VALUE! result

    Hi,

    I spent a fair amount of time yesterday to get this to work without success.
    I tried with nested IF functions but got the same outcome.
    Here is what I want to happen:
    Cell C4 (formatted general) can have 3 different content: 1. blank, 2. the word "sell", 3. the word "buy".
    The results in cell P4 (formatted currency) should be:
    1. When C4 = blank, P4 = blank (not $0.00)
    2. When C4 = "sell", P4 = blank (not $0.00)
    3. When C4 = "buy", P4 = SUM(L4:O4)

    This is what I finished up with:
    =IF(OR(C4="",C4="sell"),"",SUM(L4:O4))

    I get the right result only if C4 = "buy", both other times I always get #VALUE!

    Can someone please tell me where the bug is.

    Thanks in advance,
    George

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,637

    Re: IF(OR function keeps giving #VALUE! result

    Maybe you could post a workbook so we don't have to guess?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    Re: IF(OR function keeps giving #VALUE! result

    The obvious speculation is: that formula returning #VALUE! is importing the error from cell C4. So C4 would have the error.

    If that is not the cause, then I'd have to see an example spreadsheet to dig in deeper. So, please attach one if that's the case.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,602

    Re: IF(OR function keeps giving #VALUE! result

    May be you are trying do some other calculations with the outcome of the formula, so that is happening


    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  5. #5
    Registered User
    Join Date
    09-07-2012
    Location
    Australia
    MS-Off Ver
    Excel 16.38
    Posts
    18

    Talking Re: IF(OR function keeps giving #VALUE! result

    Thanks everybody for your quick replies.

    Would you believe I just fluked on the answer.
    What I didn't mention before is that the result in P4, when C4 says "buy", needs to be in the negative (*-1)
    I had the *-1 in the wrong place, therefore causing the error.
    So all is solved and working.
    With appreciation,

    George

    PS: can't find the spot to mark this as solved, sorry!
    Last edited by GHBrodbeck; 02-27-2018 at 08:30 PM.

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,035

    Re: IF(OR function keeps giving #VALUE! result

    Glad you have it all sorted now...

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    HTH
    Regards, Jeff

  7. #7
    Registered User
    Join Date
    09-07-2012
    Location
    Australia
    MS-Off Ver
    Excel 16.38
    Posts
    18

    Re: IF(OR function keeps giving #VALUE! result

    Sorry about this, but when I select Thread Tools, I don't get an option to mark this as solved.

    Haven't done this before so not sure about the "mark as solved" process.

  8. #8
    Registered User
    Join Date
    09-07-2012
    Location
    Australia
    MS-Off Ver
    Excel 16.38
    Posts
    18

    Re: IF(OR function keeps giving #VALUE! result

    Ok, just found it, all done thanks.

+ 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] Using Offset function as the array in the PercentRank function is giving wrong result
    By Bobneil in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-06-2013, 09:29 PM
  2. [SOLVED] Simple =Sum(B2:L2) result is not giving accurate result and =IMSUM(M2,O2) also.
    By SimonsGE in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-29-2013, 01:56 AM
  3. Formula giving different result on two different pcs
    By mdooley in forum Excel General
    Replies: 2
    Last Post: 05-28-2012, 07:05 PM
  4. Vlookup always giving n/a as result
    By ankit_85 in forum Excel General
    Replies: 4
    Last Post: 09-11-2010, 01:28 AM
  5. Formula not giving me the right result
    By Steved in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-09-2006, 10:25 PM
  6. Function giving a result for number within a range
    By TNickW@gmail.com in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-28-2006, 07:00 AM
  7. giving points by a result (function)
    By Perttu O. in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-03-2005, 04:05 PM
  8. IF function giving wrong result
    By dzorug in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-23-2005, 11:41 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