Simple Cash Book. Formula proble in Column K

[rincewind2019]

---

[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]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

---

I have put together a simple Cash book. In test 1 the formula =IF(OR(ISBLANK(A4),ISBLANK(B4)),"",D4+K3-F4) works fine to calculate Plus or Minus in the cash column.
My problem is that in test 2, where I have bank transaction I need the cash column in K4 to copy down to K5 while still calculating the change in the bank column I am using the formula =IF(OR(ISBLANK(A5),ISBLANK(B5)),"",D5+K4-F5)

Thanks in advance.

 

[Gerald Higgins]

Hi, welcome to the board.

What should the result be in column K, for Test 2 ?

 

[rincewind2019]

It should be 20 the same as the amount in the cell above. anytime I try to add to the formula it only returns a an answer as false.

 

[Gerald Higgins]

Why should it be 20 ?
Is the expenditure in F5 not a cash transaction ?

I was assuming that the formula you posted . . .
=IF(OR(ISBLANK(A4),ISBLANK(B4)),"",D4+K3-F4)
went into cell K4, to calculate the cash balance.
And the way that formula is structured suggests that F4 (and therefore F5) is relevant to the Cash Balance.

 

[rincewind2019]

ok, I'm not explaining myself very well, sorry.
In J5 I have formula =IF(OR(ISBLANK(A5),ISBLANK(B5)),"",H4+J4-I5) which in the bank column plus/minus's from the bank balance, which is fine. my problem is the cash balance in K5 needs to copy down from K4. I have the formula to work out a cash transaction but I need to add a bit to the formula that will calculate that if a bank transaction take place the cash amount needs to copy down unchanged.

Does that make sense make, I feel I'm talking utter gibberish.

 

[Gerald Higgins]

Same question again.

Is the expenditure in F5 not a cash transaction ?

Are we supposed to recognise that IF there is a value in F5, and IF there is ALSO a value in I5, then that means the expenditure was via the bank account and therefore is not relevant to the cash account ?

 

[rincewind2019]

Yes. Test 2 is a cheque coming out of the bank column so shouldn't change the cash amount but should carry down to the next cell I have the formula to calculate the bank balance but noy to carry down an unchanged cash column.

 

[Gerald Higgins]

OK I think I understand.

Let's just check I've got this right ?

Is it correct that entries in the INCOME column could be relevant to either the Bank Balance OR the Cash Balance ?
If YES, is the way to identify whether INCOME is relevant to Bank or Cash, to check if there is an entry in the DEPOSITS column ?
If there IS an entry in the DEPOSITS column, make the INCOME value affect the Bank Balance column and not the Cash column ?
If there is NO entry in the DEPOSITS column, make the INCOME value affect the Cash column and not the Bank Balance column ?

Similarly, is it correct that entries in the EXPEND column could be relevant to either the Bank Balance OR the Cash Balance ?
If YES, is the way to identify whether EXPEND is relevant to Bank or Cash, to check if there is an entry in the WITHDRAWAL column ?
If there IS an entry in the WITHDRAWAL column, make the EXPEND value affect the Bank Balance column and not the Cash column ?
If there is NO entry in the WITHDRAWAL column, make the EXPEND value affect the Cash column and not the Bank Balance column ?

If all of the above is correct, you're going to need to change your formulas on both the CASH and BANK columns.

 

[rincewind2019]

Yes all of that is correct, any help you can offer is greatly appreciated.

 

[Gerald Higgins]

OK.

Try leaving column J unchanged.

Then in K4 put this formula . . .
=IF(OR(ISBLANK(A4),ISBLANK(B4)),"",IF((H4+I4)=0,D4+K3-F4,K3))
and copy down column K as far as required.

Test if this is doing what you want.