Simple Cash Book. Formula proble in Column K

[rincewind2019]

---

a	b	c	d	e	f	g	h	i	j	k
		income		expend		bank
Date	Description	Rec	Income	Vouch	expend	CHQ No	Deposits	Withdrawal	balance	Cash
1/1	B Forward								100.00	10.00
1/1	Test 1	1	10.00	1					100.00	20..00
2/1	Test 2			1	20.00	1		20.00	80	0.00

<tbody>
</tbody>

---

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.