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]

still dont understand what you want to do

 

[etaf]

i can combine them
=IF(OR(ISBLANK(A5),ISBLANK(B5)), "", SUM($H$5:H5)-SUM($I$5:I5))
BUT really need to know what you need
this doesn't help me understand

Balance is the bank balance
Cash is onsite

the formulas you want to combine and where to put it

You know what all your columns mean and how they relate to the other columns, I dont
so saying
Cash is onsite , really does NOT help me

cash went up by 20 , but did not change the balance
next row it went down to zero and did not change the balance , assuming the deposit of 20 did change the balance

 

[rincewind2019]

Hi, I reposted with a full view of what I would like the cashbook to look like.

 

[etaf]

which post , and does it explain the questions i asked ?

 

[rincewind2019]

Hi I am trying to put together a simple cash book to run from month to month. Does anyone know what formulas I will need to put into columns J & K to calculate row 3 to 9. I have gotten so far but I am having trouble with rows 8 & 9.

Thank you.

	a	b	c	d	e	f	g	h	i	j	k
1			Income		Expenditure		Bank
2	Date	Description	Rec No	Income	F1	Expenditure	CHQ No	Deposits	Withdrawals	Balance	Cash
3	01/01/2019	B/F								£100.00	£10.00
4	02/01/2019	Cash Income	1	£20.00						£100.00	£30.00
5	03/01/2019	Cash Expenditure			1	£10.00				£100.00	£20.00
6	04/01/2019	Cheque Expenditure			2	£10.00	1		£10.00	£90.00	£20.00
7	05/01/2019	Cheque Income	2	£20.00				£20.00		£110.00	£20.00
8	06/01/2019	Cash to Bank						£10.00		£120.00	£10.00
9	07/01/2019	Bank to Cash							£20.00	£100.00	£30.00

<tbody>
</tbody>

 

[etaf]

you still haven't explained how it works

please explain each row as i'm not following exactly

i thought

d4 = 20
changes only cash
f5 f6 changes cash only

i6, h7,h8,i9 changes balance only

thats how i read the sheet

but - is it the words in column b that define what happens ?

 

[rincewind2019]

Row 3 is the balance held for the beginning of the month J3 & K3: no formulas

Row 4 is cash coming in (D4) so only the cash amount changes, formula used in K5 is =IF(OR(ISBLANK(A5),ISBLANK(B5)),"",IF((H5+I5)=0,D5+K4-F5,K4-H5+I5))

Row 5 is cash gong out F5 so only the cash amount changes, formula is the same above.

Row 6 is a Cheque going out, so is listed as an expenditure F6 and a withdrawal from the bank balance I6, The bank balance should change but the cash amount K6 should not.

Row 7 is a Cheque Coming in, so is list as an income D7 and a deposit into the bank balance H7. The bank balance should change but not the cash amount.

Row 8 is a transfer from the cash K8 to the bank balance J8, so the deposit in H8 should make the bank balance increase J8 and the cash amount should decrease K8

Row 9 is a transfer from the bank balance J9 to cash K9, so the withdrawal in I9 should make bank balance decrease J9 and the cash amount should increase K9

This simple cash book is turning out to be anything but simple.

 

[etaf]

Row 4 is cash coming in (D4) so only the cash amount changes, formula used in K5 is =IF(OR(ISBLANK(A5),ISBLANK(B5)),"",IF((H5+I5)=0,D5+K4-F5,K4-H5+I5))

Should that be looking in Row 5 - if applies to row 4 ?

Would you have an entry in column B - but with column A blank - ie NO date but a description

The expenditure column F - the description determins what balance to change
Cash Expenditure changes JUST The CASH column
Cheque Expenditure changes JUST THE BANK column

it looks to me like the changes to cash and bank balance are dependant on the Description ONLY

how many descriptions do you have , you may need to have a list of descriptions and if they apply to Cash only, Bank only or both

spreadhseet
https://www.dropbox.com/s/uc8gh9yyd62shl6/CashBook_etaf.xlsx?dl=0

 

[rincewind2019]

So how do you put in a description to work with a formula?

 

[etaf]

So it is only the desription which determines what happens to the same cell value

Cash to Bank
Cheque income
Both shown in column H
But changes different balances



As you did in your post #17
do this for ALL description you could possible put in the spreadsheet

The we can work out a formula Possible multiple Nested IFs , maybe a lookup with a indirect

but it may be complicated or simple - just depends on what ALL your possible descriptions are