#1
|
|||
|
|||
How to find the wrong number when you want to hit 0?
Hi Guys.
I am doing some accounting for a charity organization. I have one transaction account that should be 0,- in the end of the year. Several (sub) organizations use same bankterminal to save money and then I pay out that money to their account. I've importet all transactions from the bank to excel. They are sortet in columns: in (+) out (-) So if everthing was right all year the accont should be in 0,- 2458 came in I pay out 2458. Lot of transactions. Is there a way to find where the problem are. Everey number should equal it self out. Which numbers aren't? Thanks in advance. |
#2
|
||||
|
||||
Hi and welcome
please post a sample sheet (no pics please) showing data and expected results ( if needed). Click Go advanced - Manage attachments
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
1. You need a sheet Transactions with a table for imported data (Columns e.g. Organization, TransactionDate, SumIn, SumOut, optionally a calculated column TansactionYear with formula "= YEAR([@TransactionDate]"). Best will be to define this as a Table (e.g. tTransactions);
2. You need a sheet Organizations, optionally with field in 1st row where user can enter year number. When table is used for several years, and there will be a cell for year number, define this cell as Name, e.g. nRepYear. 3. On sheet Organizations, you need a table with columns Organization, Saldo (column headers must be not higher that row 3). And again, the best will be to define this as Table (e.g. tOrganizations) 4. In Table tOrganizations, fill the column Organization with list of all organizations from Table tTransactions; 5. Into 1st data row of Table tOrganizations, into column Saldo enter the formula: Code:
=SUMIFS(tTransactions[SumIn],tTransacions[Organization],[@Organization],tTransacions[TransactionYear],nRepYear)-SUMIFS(tTransactions[SumOut],tTransacions[Organization],[@Organization],tTransacions[TransactionYear],nRepYear) Current formula assumes, that in tTransactions you have separate columns for incoming and outgoing transactions, and sums in those columns are positive. Otherwise you have to make corrections in formula. In table tOrganizations, in column Saldo is displayed current saldo for every organization (for report year determined by nRepYear). |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Wrong page number | DiegoManBR | Word | 2 | 04-14-2016 09:33 AM |
workbook saved in wrong number format | EnricoHorst | Excel | 3 | 04-05-2016 12:45 AM |
How to find number of coma and then add that number of rows in word using macro? | PRA007 | Word VBA | 7 | 05-27-2015 10:45 PM |
Wrong page number 2013 | officejunkie | Word | 2 | 05-06-2015 12:57 PM |
One level 3 heading number is wrong - but all others are fine | Dr Wu | Word | 4 | 05-09-2013 10:07 AM |