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

 

[rincewind2019]

Date	Description	Rec No	Income	F1	Expenditure	CHQ No	Deposits	Withdrawals	Balance	Cash
01/01/2019	B/F								£100.00	£50.00
02/01/2019	Cash to bank						£25.00		£125.00	£25.00
03/01/2019	Bank to cash							£10.00	£115.00	£20.00

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Think I'm getting a little bogged down by asking too much.
Does anyone know of a formula that will transfer amount from cash to bank account and visa versa where the only amounts entered are in the deposits and withdrawals columns the rest of the formulas I have work perfectly its just this one that is driving me nuts.

Thank you.

 

[etaf]

then you can ADD an IF() to the normal formulas you are using and say

In cash balance column
=IF ( B3 = "cash to bank", formula to decrease cash balance, if(B3 = "bank to cash", formula to increase cash , normal cash formula )

in balance column
=IF ( B3 = "cash to bank", formula to increase balance, if(B3 = "bank to cash", formula to decrease balance , normal balance formula )

 

[rincewind2019]

in the balance column I put =IF(B3=”cash to bank”,J2+H3,IF(B3=”bank to cash”,J2-I3,J2))

In the cash column I put =IF(B3=”cash to bank”,K2+I3,IF(B3=”bank to cash”,J2-H3,J2))

The only response I get is #NAME ?

 

[etaf]

should be oK
=IF(B3=”cash to bank”,J2+H3,IF(B3=”bank to cash”,J2-I3,J2))

in a different cells try out those formulas - see if you get #name

=J2+H3
=J2-I3
=J2
see if you still get #name

are you copying - sometimes the " need to be entered and not copied

 

[rincewind2019]

Almost there the balance column works fine with =IF(B3="cash to bank",J2+H3,IF(B3="bank to cash",J2-I3,J2)) entered
But the cash column with =IF(B3="cash to bank",K2+H3,IF(B3="bank to cash",K2-I3,K2)) doesn't it goes up the same amount as the balance.

I also need to nest it with =IF(OR(ISBLANK(A5),ISBLANK(B5)), "",H5+J4-I5) in the balance column
and =IF(OR(ISBLANK(A5),ISBLANK(B5)),"",IF((H5+I5)=0,D5+K4-F5,K4)) in the cash column.

 

[etaf]

But the cash column with =IF(B3="cash to bank",K2+H3,IF(B3="bank to cash",K2-I3,K2)) doesn't it goes up the same amount as the balance.

why in the cash column do you use
K2+H3
isnt that K2 -H3, as the Cash goes into the BANK so it removes the CASH
same for BANk to cash - don't you have these the wrong way round

You need to look at the logic of the formula , as i may have got the wording not 100% but you should understand the principle

same with the nested if - IF its nether Cash to bank or Bank to cash then your normal forumla should apply

balance

=IF(B3="cash to bank",J2+H3,IF(B3="bank to cash",J2-I3,IF(OR(ISBLANK(A5),ISBLANK(B5)), "",H5+J4-I5))))
or we could do the A5/B5 test first - NOTE I have just copied and pasted - Make sure all the ROWS are referenced here correctly

=IF(OR(ISBLANK(A5),ISBLANK(B5)), "",IF(B3="cash to bank",J2+H3,IF(B3="bank to cash",J2-I3,H5+J4-I5))))

so Starting at B3, should that be
=IF(OR(ISBLANK(A3),ISBLANK(B3)), "",IF(B3="cash to bank",J2+H3,IF(B3="bank to cash",J2-I3,H3+J2-I3))))

and then the same logic for the cash

 

[rincewind2019]

Took a little bit of tweaking, but it finally works. thank you for your help and advice.

 

[etaf]

you are welcome