Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 12-26-2018, 12:00 AM
tomplom tomplom is offline Windows 10 Office 2016
Novice
 
Join Date: Dec 2018
Posts: 1
tomplom is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 12-26-2018, 12:46 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,335
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Hi and welcome
please post a sample sheet (no pics please) showing data and expected results ( if needed).
Click Go advanced - Manage attachments
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #3  
Old 12-26-2018, 05:27 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 409
ArviLaanemets will become famous soon enough
Default

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)
In case the workbook is meant for single year only, you can omit all columns TransactionDate, TransactionYear, the named range and the cell for entering report year, and second comparision in both parts of Saldo formula.
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).
Reply With Quote
Reply

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


All times are GMT -7. The time now is 07:49 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft