+ Reply to Thread
Results 1 to 8 of 8

Sumproduct produces #NUM! error, double unary operator seems to be failing

  1. #1
    Registered User
    Join Date
    09-19-2017
    Location
    New Hampshire
    MS-Off Ver
    2016
    Posts
    4

    Sumproduct produces #NUM! error, double unary operator seems to be failing

    Hello everybody, this is my first time posting here and I'm hoping someone can help me with this problem that's holding back the rest of my spreadsheet. I'm trying to use the sumproduct function to product the rate of return on a varies investment transactions.

    The formula is =SUMPRODUCT(CS[Amount]/B13,(B19/CS[XPrice])^(365/CS[Age]),--(LEFT(CS[Action],3)=$A12),--(CS[XSymbol]=B$1))-1

    When I evaluate the formula and try to troubleshoot the problem, this seems to be the part that causes the #NUM! error. Sorry I can't expand the evaluate formula window , but as you can see, it is about to evaluate the double unary operator (--) without actually transforming the FALSE,FALSE...etc.. to 0,0...etc like it did with the previous operator. I'm not really sure where to go from here so any help would be great!

    Evaluate Formula.PNG
    Error.PNG

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Sumproduct produces #NUM! error, double unary operator seems to be failing

    Hello and welcome to the forum.

    It is difficult if not impossible for us to get you a solution when we are only given a non-working formula.

    Please share with us a small sample of your worksheet along with the desired outcome of the formula.

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Sumproduct produces #NUM! error, double unary operator seems to be failing

    I will agree with 63falcondude, it is difficult to help with just a screenshot of the evaluate formula tool, especially since this part of the formula does not seem to be the source of the error.

    If I had to guess at the cause of this #NUM error, I would first guess that is is caused by an overflow error in th exponentiation step. If B19>CS[Xprice] and CS[Age] is small, then I could easily see this step overflowing. I think the first test I would run would be to pull that step out =(B19/CS[Xprice])^(365/CS[Age]), put that into a cell, and see if that gives me a #NUM error.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    09-19-2017
    Location
    New Hampshire
    MS-Off Ver
    2016
    Posts
    4

    Re: Sumproduct produces #NUM! error, double unary operator seems to be failing

    Is there a way for me to attach the spreadsheet? I tried using the attachments button but nothing happens except for this:

    Untitled.png

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Sumproduct produces #NUM! error, double unary operator seems to be failing

    To upload a workbook, click on Go Advanced > Manage Attachments > Choose File > Upload > Close this window.

  6. #6
    Registered User
    Join Date
    09-19-2017
    Location
    New Hampshire
    MS-Off Ver
    2016
    Posts
    4

    Re: Sumproduct produces #NUM! error, double unary operator seems to be failing

    Thanks! I've uploaded a slightly condensed version of the spreadsheet the problem area clearly visible on the first sheet. I want the formulas to calculate a rate of return by going through all the transactions in the table on the next sheet, and average the return of all the investments that match a certain ticker symbol. Let me know if I can elaborate on anything else in the spreadsheet. Thanks again for those looking into this!
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Sumproduct produces #NUM! error, double unary operator seems to be failing

    As I suspected, you have overflow errors in that exponentiation term.

    In the first column B19 is ~25.
    CS[XPrice] contains entries that are 1, so the base of the exponentiation is ~25
    CS[Age] also contains entries that are 1, so the exponent is 365.
    Result of these calculations are 25^365 ~2E366
    The largest number that Excel (double precision) can handle is ~2E308.
    Thus those entries in the sumproduct() overflow [Try it, put =(25/1)^(365/1) into a cell]. If any one entry in the sumproduct() overflows, the entire calculation overflows.

    I don't know the solution, because I don't understand the calculation you are trying to perform. You need a different calculation approach to avoid these overflows.

  8. #8
    Registered User
    Join Date
    09-19-2017
    Location
    New Hampshire
    MS-Off Ver
    2016
    Posts
    4

    Re: Sumproduct produces #NUM! error, double unary operator seems to be failing

    Thanks, it's great to know what's been causing it! I'll try to revise it by adding some if statements to ignore those problematic entries and then post my results!

    Cheers!

+ 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] double unary in a non boolean formula
    By niukvba in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-01-2016, 08:37 AM
  2. [SOLVED] Sumproduct produces #N/A
    By antexity in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-30-2015, 10:50 AM
  3. Help with the Double unary operator
    By amartino44 in forum Excel General
    Replies: 3
    Last Post: 09-05-2013, 04:56 PM
  4. Double unary operator
    By Blake 7 in forum Excel General
    Replies: 3
    Last Post: 11-03-2010, 05:43 AM
  5. Cell.Formula failing with single & double variables
    By EseKuent in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-04-2009, 02:36 AM
  6. Cells() produces reversed date format for double digit dates
    By tarns in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-05-2006, 09:10 PM
  7. Unary + Operator
    By xcelion in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-09-2005, 06:05 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