how can I combine these two formulas

[rincewind2019]

Hi, Anyone know how I can combine these formulas?

=IF(OR(ISBLANK(A5),ISBLANK(B5)), ""

=SUM($H$5:H5)-SUM($I$5:I5)

Thanks

 

[etaf]

the IF uses a TEST , TRUE , FALSE

you have an OR
so if A5 is blank or B5 is blank =TEST (TRUE)
if TRUE Then return a ""
what do you want if Either A5 or B5 is NOT blank (FALSE)
when do you want to apply
=SUM($H$5:H5)-SUM($I$5:I5)

 

[rincewind2019]

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[TD]j[/TD]
[TD]k[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]income[/TD]
[TD][/TD]
[TD]expend[/TD]
[TD][/TD]
[TD]bank[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Description[/TD]
[TD]Rec[/TD]
[TD]Income[/TD]
[TD]Vouch[/TD]
[TD]expend[/TD]
[TD]CHQ No[/TD]
[TD]Deposits[/TD]
[TD]Withdrawal[/TD]
[TD]balance[/TD]
[TD]Cash[/TD]
[/TR]
[TR]
[TD]1/1[/TD]
[TD]B Forward[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100.00[/TD]
[TD]10.00[/TD]
[/TR]
[TR]
[TD]1/1[/TD]
[TD]Test 1[/TD]
[TD]1[/TD]
[TD]10.00[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100.00[/TD]
[TD]20..00[/TD]
[/TR]
[TR]
[TD]2/1[/TD]
[TD]Test 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]20.00[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]20.00[/TD]
[TD]80[/TD]
[TD]0.00



[/TD]
[/TR]
</tbody>[/TABLE]
I'm trying to put together a simple cash book. I need to be able to transfer money from bank balance column J from cash K and vice versa using the deposits and withdrawals columns.
Been trying for days, any help you can offer would be great.

 

[etaf]

where do you want to put the result ?

new column K

is that to be in CASH ?
you have the brought forward figures in row 2
so in row 3 J and K

Why is balance 100 when cash has gone up from 10 to 20 , with no deposit or withdrawel

you could use an addin to post here
OR
link to dropbox , onedrive

 

[rincewind2019]

[TABLE="class: cms_table_cms_table, width: 500"]
<tbody>[TR]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[TD]j[/TD]
[TD]k[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]income[/TD]
[TD][/TD]
[TD]expend[/TD]
[TD][/TD]
[TD]bank[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Description[/TD]
[TD]Rec[/TD]
[TD]Income[/TD]
[TD]Vouch[/TD]
[TD]expend[/TD]
[TD]CHQ No[/TD]
[TD]Deposits[/TD]
[TD]Withdrawal[/TD]
[TD]balance[/TD]
[TD]Cash[/TD]
[/TR]
[TR]
[TD]1/1[/TD]
[TD]B Forward[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100.00[/TD]
[TD]10.00[/TD]
[/TR]
[TR]
[TD]1/1[/TD]
[TD]Test 1[/TD]
[TD]1[/TD]
[TD]10.00[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100.00[/TD]
[TD]20..00[/TD]
[/TR]
[TR]
[TD]2/1[/TD]
[TD]Test 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]20.00[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]20.00[/TD]
[TD]80[/TD]
[TD]20.00[/TD]
[/TR]
</tbody>[/TABLE]

Sorry didn't notice that error
Test 1 is cash coming in, so the cash column going up 10 while the bank balance stays the same.
Test 2 is a cheque being paid out, so the bank balance goes down £20 while the cash stays the same.
My problem is I can't find a formula the that while allow transfers to the bank from cash and visa versa. i though the two formulas I have found may work but I can't find a way to combine them in cell J

 

[rincewind2019]

Sorry, Test 1 cash should be £10 not £20

 

[etaf]

and so row 4 should be -10

then use in
J3
=J2+$H3-$I3
K3
=K2+$H3-$I3

then to stop blank entries - Will a blank row have a date entry in A?
if not then
change to
J3
=IF($A3="","",J2+$H3-$I3)
k3
=IF($A3="","",K2+$H3-$I3)

 

[rincewind2019]

[TABLE="class: cms_table_cms_table_cms_table"]
<tbody>[TR]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[TD]j[/TD]
[TD]k[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]income[/TD]
[TD][/TD]
[TD]expend[/TD]
[TD][/TD]
[TD]bank[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Description[/TD]
[TD]Rec[/TD]
[TD]Income[/TD]
[TD]Vouch[/TD]
[TD]expend[/TD]
[TD]CHQ No[/TD]
[TD]Deposits[/TD]
[TD]Withdrawal[/TD]
[TD]balance[/TD]
[TD]Cash[/TD]
[/TR]
[TR]
[TD]1/1[/TD]
[TD]B Forward[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100.00[/TD]
[TD]10.00[/TD]
[/TR]
[TR]
[TD]1/1[/TD]
[TD]Test 1[/TD]
[TD]1[/TD]
[TD]10.00[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100.00[/TD]
[TD]20..00[/TD]
[/TR]
[TR]
[TD]2/1[/TD]
[TD]Cash to Bank[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20.00[/TD]
[TD][/TD]
[TD]120.00
[/TD]
[TD]00.00
[/TD]
[/TR]
</tbody>[/TABLE]

table above shows what I need to happen (like I should have done in the first place, sorry)
Both formulas I would like to combine seem to work fine on there own, I would like to keep the ISBLANK parts because it just makes the whole thing look tidy.

 

[etaf]

ok,
but the formula will keep tidy

but we need the full rules of what happens

cash has increased with D3 income 10.00
but not the balance ??

 

[rincewind2019]

Balance is the bank balance
Cash is onsite