+ Reply to Thread
Results 1 to 3 of 3

Match Positive and Negative Amount To Get Zero Balance

  1. #1
    Registered User
    Join Date
    09-17-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2010
    Posts
    15

    Match Positive and Negative Amount To Get Zero Balance

    Hi all,

    What i trying to do is to get zero balance amount based on three column. this three column got positive or negative amount.

    As we can see on table below, when we sum amount H1 + G2, we will get balance 0.20 and that mean not zero balance.

    but when we sum H3,G4,H5,F6 we can get 0 balance.



    Row No Column F Column G Column H Resault
    1 -1,033.40 No Match
    2 1,033.60 No Match
    3 -1,296.30 Match
    4 1,201.40 Match
    5 1,743.45 Match
    6 -1,648.55 Match

    i need help with formula.

    here the attachment file.
    thank you so much for helping me.
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Match Positive and Negative Amount To Get Zero Balance

    Please try with helper column

    M3
    =MATCH(0,SUBTOTAL(9,OFFSET(F3:H3,,,ROW($A$1:$A$50))),)

    I3
    =IF(IFNA((ROWS(M$3:M3)-MATCH(99,M$3:M3)< LOOKUP(99,M$3:M3)),0),"","no ")&"match"
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    09-17-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Match Positive and Negative Amount To Get Zero Balance

    Hi,

    Thank you for replay,

    The formula help alot, but its made my pc not responding due to array formula.

    My real data more than 1000 line.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Bank reconciliation/matching negative with positive amount using VBA
    By jodayme in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-13-2019, 09:14 AM
  2. [SOLVED] Minimum Amount with Positive and Negative Numbers
    By melissaw07 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-02-2016, 02:06 PM
  3. [SOLVED] Sum if carrying over as a negative balance but I need it as a positive one - any ideas?
    By startuphelper in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-04-2015, 09:24 AM
  4. vba code to separate data with positive and negative amount but same criteria
    By krazyhype19 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-31-2014, 09:00 PM
  5. offsetting amount (positive and negative)
    By giomckenzie12 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-02-2013, 03:16 AM
  6. offsetting amount (positive and negative)
    By giomckenzie12 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-01-2013, 06:18 AM
  7. Change negative amount to positive.
    By George in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 07-29-2005, 01:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1