how can I combine these two formulas

R

rincewind2019

New Member
Joined
Jan 29, 2019
Messages
23
Hi, Anyone know how I can combine these formulas?

=IF(OR(ISBLANK(A5),ISBLANK(B5)), ""

=SUM($H$5:H5)-SUM($I$5:I5)

Thanks
 
Sort by date Sort by votes
still dont understand what you want to do
 
Upvote 0

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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
Click to expand...

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
 
Last edited:
Upvote 0
which post , and does it explain the questions i asked ?
 
Upvote 0
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.

[TABLE="class: cms_table"]
<tbody>[TR]
[TD="width: 24, bgcolor: transparent"][/TD]
[TD="width: 86, bgcolor: transparent"]a[/TD]
[TD="width: 152, bgcolor: transparent"]b[/TD]
[TD="width: 55, bgcolor: transparent"]c[/TD]
[TD="width: 79, bgcolor: transparent"]d[/TD]
[TD="width: 58, bgcolor: transparent"]e[/TD]
[TD="width: 87, bgcolor: transparent"]f[/TD]
[TD="width: 66, bgcolor: transparent"]g[/TD]
[TD="width: 82, bgcolor: transparent"]h[/TD]
[TD="width: 87, bgcolor: transparent"]i[/TD]
[TD="width: 80, bgcolor: transparent"]j[/TD]
[TD="width: 75, bgcolor: transparent"]k[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #C6E0B4"]Income[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #F4B084"]Expenditure[/TD]
[TD="bgcolor: #F4B084"][/TD]
[TD="bgcolor: #BDD7EE"]Bank[/TD]
[TD="bgcolor: #BDD7EE"][/TD]
[TD="bgcolor: #BDD7EE"][/TD]
[TD="bgcolor: #BDD7EE"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]Date[/TD]
[TD="bgcolor: transparent"]Description[/TD]
[TD="bgcolor: #C6E0B4"]Rec No[/TD]
[TD="bgcolor: #C6E0B4"]Income[/TD]
[TD="bgcolor: #F4B084"]F1[/TD]
[TD="bgcolor: #F4B084"]Expenditure[/TD]
[TD="bgcolor: #BDD7EE"]CHQ No[/TD]
[TD="bgcolor: #BDD7EE"]Deposits[/TD]
[TD="bgcolor: #BDD7EE"]Withdrawals[/TD]
[TD="bgcolor: #BDD7EE"]Balance[/TD]
[TD="bgcolor: transparent"]Cash[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: #BDD7EE"]01/01/2019[/TD]
[TD="bgcolor: #BDD7EE"]B/F[/TD]
[TD="bgcolor: #BDD7EE"][/TD]
[TD="bgcolor: #BDD7EE"][/TD]
[TD="bgcolor: #BDD7EE"][/TD]
[TD="bgcolor: #BDD7EE"][/TD]
[TD="bgcolor: #BDD7EE"][/TD]
[TD="bgcolor: #BDD7EE"][/TD]
[TD="bgcolor: #BDD7EE"][/TD]
[TD="bgcolor: #BDD7EE"]£100.00[/TD]
[TD="bgcolor: #BDD7EE"]£10.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]02/01/2019[/TD]
[TD="bgcolor: transparent"]Cash Income[/TD]
[TD="bgcolor: #C6E0B4"]1[/TD]
[TD="bgcolor: #C6E0B4"]£20.00[/TD]
[TD="bgcolor: #F4B084"][/TD]
[TD="bgcolor: #F4B084"][/TD]
[TD="bgcolor: #BDD7EE"][/TD]
[TD="bgcolor: #BDD7EE"][/TD]
[TD="bgcolor: #BDD7EE"][/TD]
[TD="bgcolor: #BDD7EE"]£100.00[/TD]
[TD="bgcolor: transparent"]£30.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]03/01/2019[/TD]
[TD="bgcolor: transparent"]Cash Expenditure[/TD]
[TD="bgcolor: #C6E0B4"][/TD]
[TD="bgcolor: #C6E0B4"][/TD]
[TD="bgcolor: #F4B084"]1[/TD]
[TD="bgcolor: #F4B084"]£10.00[/TD]
[TD="bgcolor: #BDD7EE"][/TD]
[TD="bgcolor: #BDD7EE"][/TD]
[TD="bgcolor: #BDD7EE"][/TD]
[TD="bgcolor: #BDD7EE"]£100.00[/TD]
[TD="bgcolor: transparent"]£20.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]04/01/2019[/TD]
[TD="bgcolor: transparent"]Cheque Expenditure[/TD]
[TD="bgcolor: #C6E0B4"][/TD]
[TD="bgcolor: #C6E0B4"][/TD]
[TD="bgcolor: #F4B084"]2[/TD]
[TD="bgcolor: #F4B084"]£10.00[/TD]
[TD="bgcolor: #BDD7EE"]1[/TD]
[TD="bgcolor: #BDD7EE"][/TD]
[TD="bgcolor: #BDD7EE"]£10.00[/TD]
[TD="bgcolor: #BDD7EE"]£90.00[/TD]
[TD="bgcolor: transparent"]£20.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]05/01/2019[/TD]
[TD="bgcolor: transparent"]Cheque Income[/TD]
[TD="bgcolor: #C6E0B4"]2[/TD]
[TD="bgcolor: #C6E0B4"]£20.00[/TD]
[TD="bgcolor: #F4B084"][/TD]
[TD="bgcolor: #F4B084"][/TD]
[TD="bgcolor: #BDD7EE"][/TD]
[TD="bgcolor: #BDD7EE"]£20.00[/TD]
[TD="bgcolor: #BDD7EE"][/TD]
[TD="bgcolor: #BDD7EE"]£110.00[/TD]
[TD="bgcolor: transparent"]£20.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8[/TD]
[TD="bgcolor: transparent"]06/01/2019[/TD]
[TD="bgcolor: transparent"]Cash to Bank[/TD]
[TD="bgcolor: #C6E0B4"][/TD]
[TD="bgcolor: #C6E0B4"][/TD]
[TD="bgcolor: #F4B084"][/TD]
[TD="bgcolor: #F4B084"][/TD]
[TD="bgcolor: #BDD7EE"][/TD]
[TD="bgcolor: #BDD7EE"]£10.00[/TD]
[TD="bgcolor: #BDD7EE"][/TD]
[TD="bgcolor: #BDD7EE"]£120.00[/TD]
[TD="bgcolor: transparent"]£10.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9[/TD]
[TD="bgcolor: transparent"]07/01/2019[/TD]
[TD="bgcolor: transparent"]Bank to Cash[/TD]
[TD="bgcolor: #C6E0B4"][/TD]
[TD="bgcolor: #C6E0B4"][/TD]
[TD="bgcolor: #F4B084"][/TD]
[TD="bgcolor: #F4B084"][/TD]
[TD="bgcolor: #BDD7EE"][/TD]
[TD="bgcolor: #BDD7EE"][/TD]
[TD="bgcolor: #BDD7EE"]£20.00[/TD]
[TD="bgcolor: #BDD7EE"]£100.00[/TD]
[TD="bgcolor: transparent"]£30.00
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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 ?
 
Last edited:
Upvote 0
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.
 
Upvote 0
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))
Click to expand...
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
 
Last edited:
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
How to sum up working hours (a different schedule)
Replies
9
Views
413
KarolinaKaralaite
K
serge
  • Question Question
#N/A cell skip.
Replies
3
Views
312
Peter_SSs
Peter_SSs
R
  • Question Question
Help to sum all cells above until blank cell
Replies
3
Views
695
RemyMoo
R
V
  • Question Question
How to combine two if/or formulas (separately)
Replies
6
Views
291
valmir
V
C
  • Question Question
Formula returning #VALUE! help
2
Replies
10
Views
174
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,830
Messages
6,181,228
Members
453,025
Latest member
Hannah_Pham93

Share this page

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