# 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.

#### Attachments

• 10.9 KB Views: 6

#### 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

• 12.4 KB Views: 5

#### Pinang

##### Member
Thanks a lot, its working fine