+ Reply to Thread
Results 1 to 15 of 15

#value! error with XIRR function

  1. #1
    Forum Contributor juntjoo's Avatar
    Join Date
    02-29-2008
    Location
    Cape Coral, FL
    MS-Off Ver
    2007
    Posts
    334

    #value! error with XIRR function

    https://www.dropbox.com/s/44wtjav5dq...rror.xlsx?dl=0

    not sure if it's because the dates come from a formula or what but I read the basic rules and don't see what could be wrong. anyone have an idea? thanks
    Excel 2010

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: #value! error with XIRR function

    Attach a sample workbook (not a picture). 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.

    I didn't say I will help you, I said only attach example excel file
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  3. #3
    Forum Contributor juntjoo's Avatar
    Join Date
    02-29-2008
    Location
    Cape Coral, FL
    MS-Off Ver
    2007
    Posts
    334

    Re: #value! error with XIRR function

    Quote Originally Posted by sandy666 View Post
    Attach a sample workbook (not a picture). 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.

    I didn't say I will help you, I said only attach example excel file
    Idk how that happened. I'll try again.

    https://www.dropbox.com/s/44wtjav5dq...rror.xlsx?dl=0

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: #value! error with XIRR function

    To attach a file to your post,

    click Go Advanced,
    scroll down until you see Manage Attachments,
    click that and select Browse,
    select your file and click Open,
    click Upload and you will see your attachment below Upload Files from a website
    click Close this window,
    click Submit reply


    After that you should see attachment in your post

    Not everyone wants download files from external servers

  5. #5
    Forum Contributor juntjoo's Avatar
    Join Date
    02-29-2008
    Location
    Cape Coral, FL
    MS-Off Ver
    2007
    Posts
    334

    Re: #value! error with XIRR function

    There's nothing wrong with the link. Try it

    Edit: just read your last statement -oh, okay. In a sec

  6. #6
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,597

    Re: #value! error with XIRR function

    First value is negative & rest of values need positive.
    Secondly, If any number in dates is not a valid date, XIRR returns the #VALUE! error value.
    In same file i have made manually changes in data. Its work. Refer attach file.
    Attached Files Attached Files


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  7. #7
    Forum Contributor juntjoo's Avatar
    Join Date
    02-29-2008
    Location
    Cape Coral, FL
    MS-Off Ver
    2007
    Posts
    334

    Re: #value! error with XIRR function

    thanks, but the original first value WAS negative, so not sure what you were pointing that out for. Also, I need those formulas in there that you removed. Any idea about why my version didn't work though?

  8. #8
    Forum Contributor juntjoo's Avatar
    Join Date
    02-29-2008
    Location
    Cape Coral, FL
    MS-Off Ver
    2007
    Posts
    334

    Re: #value! error with XIRR function

    here's the file attached. Is there a limit to what can be held in here? I usually use external servers so I never run out of space. happens with some forums/sites
    Attached Files Attached Files

  9. #9
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,597

    Re: #value! error with XIRR function

    Check your computer system date format.

  10. #10
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: #value! error with XIRR function


  11. #11
    Forum Contributor juntjoo's Avatar
    Join Date
    02-29-2008
    Location
    Cape Coral, FL
    MS-Off Ver
    2007
    Posts
    334

    Re: #value! error with XIRR function

    for what and what to look for? btw the result is the same whether I'm on my laptop or android phone.

  12. #12
    Forum Contributor juntjoo's Avatar
    Join Date
    02-29-2008
    Location
    Cape Coral, FL
    MS-Off Ver
    2007
    Posts
    334

    Re: #value! error with XIRR function

    what's this? a pic of file formats? no comprendo

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

    Re: #value! error with XIRR function

    I am not familiar with the financial functions or with many of the details of these business math calculations. A few tests I tried:

    1) My first question was whether it was a problem with the boolean values in rows 12+. By removing the structured table format (convert structured table to range), I was then able to specify that the function should only use rows 3:11 in the XIRR calculation rather than the entire table. However, this yielded a NUM error.

    2) In order to get a good data set where I knew the expected outcome, I copied the sample data from the help file (https://support.office.com/en-us/art...b-a303ad9adc9d ). With those data I get the expected 37.34%. By changing the last value to FALSE, I get the #VALUE error. By reducing the rows by 1, I get 3.38% (which I assume is the correct XIRR() for the first 4 entries from the help file's sample data).

    At this point, my hypothesis is that the XIRR() function does not know how to handle those boolean (or other non-numeric) values in its input range, and is, therefore, returning the #VALUE error. I think further debugging requires knowing what your expected outcome should be and how you would expect the XIRR() function to handle those FALSEs.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  14. #14
    Forum Contributor juntjoo's Avatar
    Join Date
    02-29-2008
    Location
    Cape Coral, FL
    MS-Off Ver
    2007
    Posts
    334
    Quote Originally Posted by MrShorty View Post
    I am not familiar with the financial functions or with many of the details of these business math calculations. A few tests I tried:

    1) My first question was whether it was a problem with the boolean values in rows 12+. By removing the structured table format (convert structured table to range), I was then able to specify that the function should only use rows 3:11 in the XIRR calculation rather than the entire table. However, this yielded a NUM error.

    2) In order to get a good data set where I knew the expected outcome, I copied the sample data from the help file (https://support.office.com/en-us/art...b-a303ad9adc9d ). With those data I get the expected 37.34%. By changing the last value to FALSE, I get the #VALUE error. By reducing the rows by 1, I get 3.38% (which I assume is the correct XIRR() for the first 4 entries from the help file's sample data).

    At this point, my hypothesis is that the XIRR() function does not know how to handle those boolean (or other non-numeric) values in its input range, and is, therefore, returning the #VALUE error. I think further debugging requires knowing what your expected outcome should be and how you would expect the XIRR() function to handle those FALSEs.
    Thanks. Do so you or anyone know how to remove the "false"s? I tried doing an if(x="false", then 0) which would work but I don't think excel reads it as the word "false" unless the problem was something else

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

    Re: #value! error with XIRR function

    but I don't think excel reads it as the word "false" unless the problem was something else
    Correct, the boolean value FALSE is different from the text string "false". In the format you described, the function become if(x=FALSE, then 0) (note that I did not enclose FALSE in quotes).

    However, I'm not sure an additional IF() function is necessary. Looking at your formula in column D, you did not specify a value for the "value_if_false" argument in the IF() function (inside of the IFERROR() function), which is where the FALSE values are coming from. It seems like all you should need to do, if you want to return 0 instead of FALSE, is to put a 0 (without quotes, or it will return the text string "0" which is not the same as the number 0) in that argument. https://support.office.com/en-us/art...c-aa8bbff73be2

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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