I have a trial balance that is NOT in debit and credit format. I need to change the signage.
1. Liability starts with account 200000 to 399999. Any amounts in these accounts need to be credit with a (minus) sign.
2. Revenue starts with 400000 to 499999 & "ICPRev". Any amounts in these accounts need to be credit with a (minus) sign.
Assets (acct#1*) and expense (acct#5* to 9*) are already correctly presented with Dr/plus. So, we can ignore them.
I'm trying to use the IF then condition from the Formula Tool but no luck. Hope someone can help. Thanks in advance.
Solved! Go to Solution.
Hi @Taxguy03 ,
the problem seems to be related to data types. Account is a string data type, to compare it against a nueric range, you have to convert to numbers using ToNumber.
In addition, I think the [ABC CO] column has to be multiplied by -1. I've modified your workflow. Hope, this is helpful.
Best,
Roland
Hi Roland,
Good morning. This is a great solution. I very much appreciated. Other quick question.
I have a General Ledger Account which is has string data type. The account name is >> ICPRev. It's a revenue accounts so needs to be minus sign.
Is there a formula to flip the sign for this account (ICPRev) ? You will see that account in the excel file that was originally attached.
Thank you an advance for your help.
Hi @Taxguy03 ,
you could add one more condition to the IF/ELSEIF/ELSE that already exists:
ELSEIF [Account] = 'ICPRev' THEN
[ABC CO] * (-1)
In case there are additional accounts you need a sign flip, you can alse use:
ELSEIF [Account] IN('ICPRev', 'additional account') THEN
[ABC CO] * (-1)
(replace 'additional account' by the accounts you need)
Does this help?
Best,
Roland
Thank you, Roland. You saved my tons of hours. Hope to talk to you soon again and have a great day for now.
Hi Roland,
Sorry to bother you again. I have another issue which I need your help. I used your workflow from Monday and added few tools at the front of the workflow to convert a current columnar format trial balance to stack format (comparable 2018 & 2019). The output doesn't seem right. I don't think the accounts are lining up correctly as I may not have the Summarize tool condition properly setup.
I really hope you can help.
Attached:
1. Workflow
2. Sample raw data
3. Output should be (answer)
Hi @Taxguy03 ,
happy to help, don't worry.
I modified the workflow a bit, especially changed "Join" to "Union" for the files after adding a year. Added some comments. Let me know, if it works.
Best,
Roland
Hi Ronald,
I have another favor to ask. I need to verify the (income) loss by Entity by performing summation for any account that is greater than 399999 (basically any accounts that starts with *4 to *8, the revenue and expense accounts). I have attached the stack Trial balance you helped to put together. You can ignore accounts from *1 to *3.
Hope you can help.