{ "media_type": "text", "post_content": "Can someone please tell me the formula for finding all values that net to zero?", "post_id": "5f971424ec3e530021bfb21c", "reply_count": 16, "vote_count": 3, "bowl_id": "564a5cdb94887803001dd07c", "bowl_name": "Accounting", "feed_type": "crowd" }
null

Can someone please tell me the formula for finding all values that net to zero?

like
Posting as :
works at
You are currently posting as works at

Jesus. You can’t filter for zero for things that NET to zero when added

- there are eData routines that will find those for you. At KPMG, you can ask the offshore team to do it
- make an absolute value column, then sort it in ascending order, your netting numbers will be next to each other. You’ll have to be careful to make sure you’re not accidentally taking out actual duplicates though. Works better on a smaller population

like

Add a column of ones next to all the values. In the next column, multiply the values by the ones. Then sum this column at the bottom. Use solver to set the summed cell to 0, while changing the cells with the ones. Restrict them to be binary so it is either 1 or 0. It should solve to find all the values that net to 0. Or it will just turn all the ones to 0s which would also sum to 0.

Usually I only use this with all positive values and I’m looking for a specific summed value other than 0.

like

Should I keep going ?

likefunny

Lol KPMG 1 is stoopid

like

I think you’re thinking of the solver function in excel - it’s an add-on that takes some skill to use but you can use it to “solve” for zero.

like

Tried that, but the population is too big :/

I apologize as these instructions may be hard to follow, but I figured out this method when I had to net 32,000 line items from Operating Expense testing detail. I wish you the best of luck.

1) Assign Absolute Value to Amount Column
2) Sort the absolute value column by largest to smallest
3) Create additional column
4) Merge EVERY two cells together in the new column (i.e. combine cells 1 and 2; 3 and 4; 5 and 6)
5) Use the formula =cell1 + cell2 in the newly merged cells
6) Create additional column
7) Use the following formulas in cells 1 to 4 in this order
a. =If ( Cell 1 = 0 , “TM” , “” )
b. =If ( Cell 1 = 0 , “TM” , “” )
c. =If ( Cell 3 = 0 , “TM” , “” )
d. =If ( Cell 3 = 0 , “TM” , “” )
8) After you have that sequence so that it will do rows 1, 3, 5, 7 etc… highlight the 4 formulas you just created and copy paste them to the bottom.
9) That should net many duplicates

If you need to remove even more entries do the next steps:
1) Clear all filters
2) Sort the data by the TM column so that all TMs are at the top (TMs were created in step 7 above).
3) Start the above process over again starting at the first line item that does not have a TM.
4) Repeat until you remove duplicates.

like

=countif

like

Yeah, I’ve had only limited success with the solver function. That’s the only thing that I’m aware of that fulfills that function.

like

Send to your offshore team in india

like

Or filter for 0

Or conditional formatting to find 0

Add a column. Take abs value of original data. Sort and see which ones net. Can get fancy with if cell in row 2 - cell in row 1 = 0 to help but might not be exact

Excel power query

Additional Posts in Accounting

Me: I hate office drama!
Also me:

Post Photo
like

Aspiring to be a management consultant

I am currently working with Hyundai as Assistant manager.

I desire to join MBB in consulting line

I don't hold an MBA degree but have rich experience in S&OP

Can someone help me

Question on Becker. I saw on the website that the learning material will be updated in June. Does anyone know the exact date based on experience?

like

Traveling out to Montana/Idaho/Wyoming early September. First time out west / big sky country. Any recs for activities/food/breweries are much appreciated. Have a rough itinerary that includes a couple different local activities/the national parks.
Thank you!!

like

Anyone need Becker’s CPA books all four part brand new. Not used at all.

like

Really wish this became the norm

Post Photo
like

Hi i got a mail from a IFS team from US about my adverse response in the FY22 ACC regarding EBP's. Please let me understand what's this
I am unable to understand this.

like

What is the level Amazon India offers for 8+ years experience developer ? Is it level 5 or 6 ?

like

Any insight on Anthesis Group for sustainability consulting?

like

Hi,

Am looking for network security opportunities total experience in cyber security 6yrs (3yrs in endpoint security and 3yrs in network security)
Currently pursuing CEH

Kindly refer

like

I got offer for project specialist at Deloitte. Can any one help hierarchy of designations and wha my type of work mostly it will be?Deloitte

likefunny

I’m so poor. My expense is like 5000 dollars a month and I net in about 5000 after tax retirement etc. My ex has been helping with about 2000 a month but who knows how long he would help. In theory I make 6 figures in a MCOL city; so is that normal for everyone? I don’t even have an emergency fund (I have some money in brokerage if needed).

likefunny

I’ve gone on dates with guys who started off wanting to be in a relationship with me but at the end of it they say they don’t want to date me, but that they want to have fun. Was that their intention

like

I am a data scientist with 8 yeo.
I have offer from
EXL Service and Absolutdata
Both are offering the same package.
Any suggestions which one to go with?

like

Urgent Guys, On IT declaration portal i declare 2 lakh as investment which will save me 40k tax, but what happens if i fail to invest whole 2 lakh and can not show them investment proof?

like

How do you bring up exclusivity with someone? I asked if he was seeing other people a couple weeks ago, he said no but was REAL weird about it and we haven’t gone back to that convo since but we’ve continued seeing each other and it hasn’t been awkward. Im not sticking around if we don’t move forward but idk how to bring it up. Im 25f, he’s 28.

like

Any of the big4 sponsoring H1B transfers right now?

like

D Strategy consultant looking to potentially move to Facebook - to FBers, how is it there?

like

SHOULD I ACCEPT THE OFFER?

I'm at a target uni for Big 4 accounting. I applied for and received an offer for Summer 2023 Risk&FinancialAdvisory - Technology internship w Deloitte. I'd like to get into Big 4 consulting ideally when full-time. I'm an engineering student - I'm not going to be interested in IT audit. Summer 2023 will be the summer after sophomore year, so I'll be declining the full-time return offer and I'll be looking for a different internship after junior year.

like

Additional Posts (overall)

There are only two groups of people in this world... those that chew gum and those that don’t.

like

What is CohnReznick paternity leave policy?

like
like

What’s the typical accounting career path?

like

Gold Cup Quarter-Finals tonight - who y'all got?

like

KPMG to require vaccine or negative test to enter KPMG facilities effective 10/4/21.

likefunnyhelpful

ladies, im giving myself a hernia trying to carry all my crap for work (especially because of this hoteling nonsense). any ideas on backpacks or purses that will help my lopsided-ness. TIA ☺

like

I saw a post on here earlier about the boldest expense you ever put through. I'm just curious how have you not been caught?

like

Does anyone watch stranded with a million dollars? Alonzo is the biggest idiot on there.

like

Anyone have any insights on being financial analyst/performance analyst for companies like state street, citco, and other companies like those? Are they just fund administrators with a fancier name?

like

Is there a minimum period you need to be employed to be eligible for maternity leave benefits?

like

What do you do when you said you would get something done by end of day but completely run out of brain power

likehelpful

Staff 2 audit thinking about making a move to an industry job. Would it make a difference if I wait for promotions in Oct to start looking or are there similar opportunities available now? Have my CPA

likehelpful

DAE wish they were white? Is it to much to ask for basic respect and fair promo opps??? 😔

likefunny

Has anyone gone CAIA route ?

likehelpful

Best part of having a second monitor? Streaming Masters coverage all day ⛳️🏌

like

What are the personalities of each big 4? Is there really a differentiator in people across each big 4?

likehelpfulfunny

I use to think I was the only lonely auditor at the bar by myself... Truth is there are also auditors at the bar from the other big 4. Cheers to being miserable

like

Am I supposed to tip the leasing agents that work in the front desk of my apartment complex?

like

For EY if you get an offer how long does it take after the last interview and when do you find out the salary?

like

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
Download Fishbowl to see what others are saying
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here

Share

Embed this post

Copy and paste embed code on your site

Preview

Download the Fishbowl app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy.

Already have an account? Log in

New to Fishbowl?

image with Fishbowl logo and arms reaching out for a handshake

Already have an account? Log in

By continuing you agree to Terms of Use and Privacy Policy.
Messaging rates may apply

For account settings, visit Fishbowl on Desktop Browser or

General

Legal