+ Reply to Thread
Results 1 to 5 of 5

Getting the Improduct Formula to compute

  1. #1
    Registered User
    Join Date
    10-16-2017
    Location
    Charlotte
    MS-Off Ver
    2016
    Posts
    31

    Getting the Improduct Formula to compute

    Attached I have a word document with a formula, and an excel document to copy the formula into. I found that when I put the formula into the excel document it gives an error message, but I get a numerical value when I put in either of the components of the formula. I found that having ordinary formulas in a complex formula usually seems to work so long as all the ordinary formulas are on real numbers.

    Also in order to get the excel file to attach I cleared the formulas from some of the cells. Fill down from B2 to B100001 before putting the formula in.

    Also the formula in the word document is an array formula, so you will need to click Control-Shift-Enter when pasting the formula into a cell.

    What could be causing the error message and how do I fix it?
    Attached Files Attached Files
    Last edited by ancog; 12-08-2020 at 01:40 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,390

    Re: Fixing the Error in this Formula

    ancog,

    Can you provide something with a smaller sample of dummy data?

    Asking for 100k lines to be created does seem somewhat excessive.

    Ochimus

  3. #3
    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,810

    Re: Fixing the Error in this Formula

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    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

  4. #4
    Registered User
    Join Date
    10-16-2017
    Location
    Charlotte
    MS-Off Ver
    2016
    Posts
    31

    Re: Getting the Improduct Formula to compute

    @alansidman I edited the title and added something to my post. Are the changes I made sufficient?

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

    Re: Getting the Improduct Formula to compute

    It looks like you are coming up with an array of 100000 complex numbers and trying to multiply them all together, is that correct? I note that the help file for the IMPRODUCT() function states that it can only handle up to 255 complex numbers (assuming the documentation is correct, which is not always guaranteed https://support.microsoft.com/en-us/...e-8ba7aab3a5ba ). I expect your error is related to too many arguments for the IMPRODUCT() function.

    Digging a little deeper, it looks like each element of the array is approximately 250i. 250i^100000 is likely to overflow the processor. For that matter, anything raised to the 100000 power is likely to overflow/underflow -- and that would apply to any programming language using double precision floating point for the calculation.

    My older version of Excel doesn't have the GAMMA() function, so I cannot fully test what you are doing here. However, from what I have noted above, I don't think the error is related to the GAMMA() function.

    I guess at this point, I would ask what your function is supposed to be doing. Is it supposed to be multiplying 100000 complex numbers together to give you one final result, or is it supposed to return 100000 products of 2 complex numbers? If the latter, I would make that a helper column in my spreadsheet rather than try to bury it in a single formula -- but then I am not averse to helper columns like so many Excel users. If the former, you probably need to think through your calculation more carefully -- not only to make the calculation fit within the limits of Excel's functions, but also to avoid floating point overflow/underflow errors.

    Have I correctly understood what your formula is trying to do? If nothing else, this should bump your thread back to the top for others to see.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ 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] Fixing an axis error
    By agrwl.anmol in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-16-2019, 10:51 AM
  2. [SOLVED] Fixing runtime error '53'
    By Bronx in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-03-2016, 09:28 AM
  3. Fixing Date Error -
    By JCL622 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-07-2013, 10:54 AM
  4. [SOLVED] Help me in fixing the error
    By kishoremcp in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-26-2012, 07:26 AM
  5. Fixing a sorting error for empty rows (error '1004')?
    By StargateFan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-02-2005, 06:20 PM
  6. Fixing a sorting error for empty rows (error '1004')?
    By StargateFan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-02-2005, 06:17 PM
  7. Fixing the #DIV/0! Error
    By kc2equ in forum Excel General
    Replies: 1
    Last Post: 02-22-2005, 11:32 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