#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

Originally Posted by sandy666

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

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.

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?

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

Check your computer system date format.

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

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

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.

Originally Posted by MrShorty

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

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