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
Bookmarks