+ Reply to Thread
Results 1 to 4 of 4

Add all absolute values of items in excel without using abs() for each range

  1. #1
    Registered User
    Join Date
    12-14-2018
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    5

    Add all absolute values of items in excel without using abs() for each range

    I have to do a sum of the absolute value of all of the items in many different tables in excel. Is there a way I can do this without typing abs(range) for each range that I want to sum.

    What my data looks like:

    Header | Header | Total
    XXXX | XXXXX | XXXX
    XXXX | XXXXX | (XXX)
    XXXX | XXXXX | Subtotal


    Header | Header | Total
    XXXX | XXXXX | XXXX
    XXXX | XXXXX | (XXX)
    XXXX | XXXXX | Subtotal

    Header | Header | Total
    XXXX | XXXXX | XXXX
    XXXX | XXXXX | (XXX)
    XXXX | XXXXX | Subtotal


    I would need to take the sum of the absolute values of all of the totals. I've tried doing and array formula such as {sum(abs(C2:C4;C7:C9;C12:C14))}, but this doesn't actually work. Now I could do a sum where I use an array formula, and do ABS(range) with every table individually such as {sum(abs(C2:C3);abs(C7:C8);abs(C12:C13)))}, but there are much more than 3 tables, and this would be quite time consuming. Is there a better way of approaching this?

    Thanks

    (edit) - changed the tables to show that there are subtotals which must be excluded in my table
    Last edited by lolun; 01-11-2019 at 04:10 PM.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,670

    Re: Add all absolute values of items in excel without using abs() for each range

    It sounds like all of the data is in one column. How about something like this:

    =SUM(IFERROR(ABS(C2:C14),0)) Ctrl Shift Enter

  3. #3
    Registered User
    Join Date
    12-14-2018
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    5

    Re: Add all absolute values of items in excel without using abs() for each range

    Thanks 63falcondude,

    However, my table actually has subtotals which I must exclude from my sum. I've updated my original post to show this

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,670

    Re: Add all absolute values of items in excel without using abs() for each range

    I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

    To upload an Excel workbook, follow these steps:
    1) Click on "Go Advanced"
    2) Click on "Manage Attachments"
    3) Click on "Choose File"
    4) Choose your file and click on "Open"
    5) Click on "Upload"
    6) Click on "Close this window"

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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