Results 1 to 3 of 3

Formula to determine which values Contra each Other

Threaded View

  1. #1
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2024
    Posts
    2,876

    Formula to determine which values Contra each Other

    I have a formula to contra Debit and Credit Values in Col, H by returning a true of false (these contra entries will sum to zero)

    However, if there are 2 debit amounts for e.g. 100 each and one credit for -100, then the formula will return "True" for all 3

    I need the formula to be amended to return "True" only for items that Contra out in Col H i.e match the debit or credit that appears first and then match the rest


    I have highlighted the item in h4 that returns 'true" i.e., but should be "False" as it has occurred a gain but has no contra entry i.e. a credit for the same amount that will sum it to zero

    my formula is

     =SUMPRODUCT(--(ABS($H$2:$H$17)=ABS(H2)))>1

    it would be appreciated if someone could kindly amend my formula
    Attached Files Attached Files
    Last edited by Howardc1001; 07-05-2021 at 07:02 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula to determine if values are within range from a lookup
    By jars104 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-15-2019, 10:21 AM
  2. Excel Formula/VBA-Conditional Sum to find contra from multiple entries
    By Miratshah in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-11-2019, 07:01 AM
  3. [SOLVED] Using cell values to determine formula range
    By milkychips in forum Excel General
    Replies: 6
    Last Post: 01-01-2016, 03:14 PM
  4. Contra items - multiple lines
    By RossInk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-13-2015, 03:33 PM
  5. Determine formula for calculating values in a table.
    By Carissa in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-25-2013, 10:28 AM
  6. Macro to identify contra entries and then preparing summary for same
    By caavimak in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-06-2012, 01:04 AM
  7. Formula to determine lowest 5 of 7 values?
    By acjcanada in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-11-2008, 10:41 AM

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