Simple Cash Book. Formula proble in Column K

rincewind2019

New Member
Joined
Jan 29, 2019
Messages
23

[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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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 ?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top