Excel 2013 - Subtract from cell 2 negative value in cell 1, but only if cell 1 is negative.

Max99000

Honorable
May 18, 2013
8
0
10,510
Can someone please help me find the right formula for this spreadsheet?
I need to subtract from the "Savings Account" to make the "Current Account Bank Balance" 0 (ZERO) only IF the "Current Account Bank Balance" is negative, otherwise not. Also, if the negative balance of "Current Account Bank Balance" is greater than the positive balance of "Savings Account" subtract the entire savings value even if it "Current Account Bank Balance" does not = 0.

Thanks!

screenshot here:
98ka.png


here is link to spreadsheet file:
PrototypeSpreadsheet
 

noidea_77

Honorable
Jul 28, 2012
406
0
11,210

";" instead of ","? And what's the Excel comment (behind the red rectangle) in column H? A format problem?
 

Max99000

Honorable
May 18, 2013
8
0
10,510


Ingnore my last post. The error says "Inconsistent Calculated Column Formula", that's because I used different formulas in the rows.

 

Max99000

Honorable
May 18, 2013
8
0
10,510


Alright. I made some changes and I found a formula that could work. This will calculate cell "I13" "=IF((G13+H12)<0,(I12+C13)+(G13+H12),(I12+C13))" (screenshot below)
However, I need to make some changes because the formula will take the "Savings Account" on minus and that is not correct. I need it to make the "Current Account Bank Balance" positive, but only if there are enough savings. Otherwise, substract from savings as much as possible (until its 0), but not below 0.

8wt1.png

 

Max99000

Honorable
May 18, 2013
8
0
10,510
I found a solution.
The SUMIFS formula is not exactly what I needed. I used an IF statement combined with MAX formula.

Example:
To calculate I13 - =IF((G13+H12)<0,MAX(((I12+C13)+(G13+H12)),0),(I12+C13))

Thanks for the help guys!
 

TRENDING THREADS