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
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 1.0.1 (16.0.14326.20140) (Android)
Excel 2010 14.04.4760.1000
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
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
There's nothing wrong with the link. Try it
Edit: just read your last statement -oh, okay. In a sec
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.
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".
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.
see this one:
https://www.excelforum.com/attachmen...e-filesize.jpg
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 shg
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).but I don't think excel reads it as the word "false" unless the problem was something else
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks