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
0
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:


here is link to spreadsheet file:
PrototypeSpreadsheet
 

Max99000

Honorable
May 18, 2013
8
0
10,510
0
Can anyone help me with the formula. I have no idea what to do. I have tried IF and SUMIF functions but I could not get it to work.
 

noidea_77

Honorable
Jul 28, 2012
406
0
11,210
60

";" 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
0


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
0


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.


 

Max99000

Honorable
May 18, 2013
8
0
10,510
0


How can I apply that to my spreadsheet? Can you give me an example please?
I'm a complete noob at this.
 

Max99000

Honorable
May 18, 2013
8
0
10,510
0
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!
 
Thread starter Similar threads Forum Replies Date
B Apps General Discussion 2
N Apps General Discussion 1
S Apps General Discussion 5
N Apps General Discussion 2
L Apps General Discussion 5
B Apps General Discussion 4
K Apps General Discussion 1
R Apps General Discussion 1
7 Apps General Discussion 37
J Apps General Discussion 11
E Apps General Discussion 15
L Apps General Discussion 5
S Apps General Discussion 1
A Apps General Discussion 1
E Apps General Discussion 1
T Apps General Discussion 1
B Apps General Discussion 1
B Apps General Discussion 23
B Apps General Discussion 2
H Apps General Discussion 13

ASK THE COMMUNITY