Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-26-2018, 12:00 AM
tomplom tomplom is offline How to find the wrong number when you want to hit 0? Windows 10 How to find the wrong number when you want to hit 0? Office 2016
Novice
How to find the wrong number when you want to hit 0?
 
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 How to find the wrong number when you want to hit 0? Windows 7 64bit How to find the wrong number when you want to hit 0? Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #3  
Old 12-26-2018, 05:27 AM
ArviLaanemets ArviLaanemets is offline How to find the wrong number when you want to hit 0? Windows 8 How to find the wrong number when you want to hit 0? Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
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
How to find the wrong number when you want to hit 0? Wrong page number DiegoManBR Word 2 04-14-2016 09:33 AM
How to find the wrong number when you want to hit 0? 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

Other Forums: Access Forums

All times are GMT -7. The time now is 10:51 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft