+ Reply to Thread
Results 1 to 5 of 5

Formula required

  1. #1
    Registered User
    Join Date
    08-29-2013
    Location
    Manchester UK
    MS-Off Ver
    Excel 2010
    Posts
    2

    Formula required

    Imagine a simple chart, where B4 = 6 and C4 = 7
    B5 = 15 and C5 = 10
    B6 = 4 and C6 = 8


    I require cell D4 to be B4-C4
    cell D5 to be B5 -C5
    cell D6 to be B6-C6

    From the above data you would get -1, +5, and -4 respectively.

    However, I require to add the resulting figures of D4 to D6 ignoring the minus and plus figures eg. D7 to be 10 and not zero.

    What would the formula for D7 be?

    Please help

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Formula required

    Use this array* formula:

    =SUM(ABS(D4:D6))

    *An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual <Enter>.

    Hope this helps.

    Pete

  3. #3
    Forum Contributor
    Join Date
    12-30-2012
    Location
    Israel
    MS-Off Ver
    2007, 2016
    Posts
    642

    Re: Formula required

    Hi. use ABS function.
    For D7 type =ABS(D4)+ABS(D5)+ABS(D6)
    Oh I've just seen more elegant soultion, but for minor use you can also use mine

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula required

    Hi and welcome to the forum

    Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request.

    To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    08-29-2013
    Location
    Manchester UK
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Formula required

    Hi Pete-UK and GIS2013

    Thanks for your kind assistance. I have used Pete's suggested formula

    and what a result! Thanks again

+ 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. Formula required to hide results from another cell formula
    By 917 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-07-2013, 12:12 AM
  2. Formula required
    By coreytroy in forum Excel General
    Replies: 2
    Last Post: 10-21-2010, 07:21 PM
  3. Formula required
    By oliver79 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-17-2008, 05:01 PM
  4. Formula Required Please
    By gunny1979 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-24-2006, 08:49 AM
  5. Help Required With IF AND Formula
    By Ian in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-25-2005, 09:06 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