I have a formula mathematically operatiing on many numbers going to a single cell like this A1 =-200+210-325-87+412-60+88 with negatives denote cash outs and positives denote cash ins then return the profit or loss. In order to calculate the ROI(Return on Inventment) I need another cell say it B1 =A1/sum(negatives of A1). Is there any array formula or something else to get the sum of all the negatives in A1?
Hi Julian,
If you have Microsoft 365 you can use the new dynamic array formulas. e.g. where your sum is in cell A2 you can use this formula:
=-SUM(VALUE(FILTER(TEXTSPLIT(FORMULATEXT(A2),,{"-";"+"}),DROP(TEXTSPLIT(FORMULATEXT(A2),,TEXTSPLIT(FORMULATEXT(A2),,{"-";"+"})),-1)="-")))
In the file attached I have broken down the steps for the formula above so you can see how it calculates.
The individual functions are explained here.
If you don't have Microsoft 365, then I would avoid stuffing cells with hard keyed sums like this as I'm not sure there's a way to do this before dynamic array formulas. If the values are in their own cells, then you can easily sum them with SUMIF.
Mynda
Hi Mynda,
I just know it. Nothing can beat you!
Be honest, I do get some solutions so far but yours is the best. The most amazing portion is that you combined the DROP and TEXTSPLIT functions to get the delimiters "+" & "-" . By the way, I found without DROP function, TEXTSPLIT(FORMULATEXT(A2),,B2#) may return the signs as well. Anyway, I love your marvelous solutions so much, I love you too.
p.s. It’s my honor to share your unparalleled solution to Microsoft Community. Please click the following link.
Julian
Glad you liked it, Julian and thanks for sharing my solution in the other forum.
For future reference, please note that you must always make it clear that you have cross posted in another forum. It is courteous to those who give up their time volunteering to help you.
Mynda
Hi Mynda,
Sorry, I can't get your point. I really have no idea what I've done wrong. I surely respect and appreciate all the instructors' efforts having replied me whether my questions were solved or not. Therefore, could you please tell me more clearly which area I must pay attention to so that I won't make the similar mistakes next time.
Best regards,
Julian
Hi Julian,
When you first make the post, in our forum or any other forum, when you've also posted the same question elsewhere, you must state that you have also posted your question in another forum and share a link to that forum post accordingly. This is to allow those who are helping to check if your question has already been answered in the other forum, so they can avoid wasting time duplicating work that's not required.
Hope that clarifies, but please let me know if you have further questions.
Mynda
