• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Open amount date required with formula

Pinang

Member
Hi,

I am looking for a formula where I can get date of open balance against each projects. refer attached file for data and result.

I have tried many formulas but didn't worked for me, can anyone able to get result with a formula in column G.

Thanks in advance.
 

Attachments

Chihiro

Excel Ninja
Without you explaining logic behind reasoning to arrive at the solution. Don't think anyone can help you.

I initially thought last negative value date, but that doesn't explain 100-2 or 100-05. Without detailed explanation we would be stabbing in the dark as to your reasoning.

Edit: From the looks of it, you'd need to iterate over range. As you are looking for sum of "Absolute" negative values that is greater than sum of all positive values for project. Traditional formulas are not meant for this type of calculation. Best bet is to use helper column on the data or use VBA/UDF.
 
Last edited:

Chihiro

Excel Ninja
Here's one way to do this.

In D3:
=IF(ABS(SUMIFS($B$3:B3,$B$3:B3,"<0",$A$3:A3,A3))>SUMIFS($B$3:$B$22,$B$3:$B$22,">0",$A$3:$A$22,A3),"Y","N")
Copy down.

Then in H3:
=IF(G3>=0,"-",AGGREGATE(15,6,$C$3:$C$22/(($D$3:$D$22="Y")*($B$3:$B$22<0)*($A$3:$A$22=F3)),1))

Copy down.
 

Attachments

Top