|
Klassieke bestandstypen zijn vanaf januari 2021 niet meer beschikbaar voor gebruik. U kunt uw klassieke bestanden omzetten of een PDF downloaden. Meer informatie

sum(abs(range)) or alternatives

Demo aanvragen

I am looking for the ability to sum the absolute values within a range but the functions which allow this in excel do not currently work in Workiva. Is that an alternative which would allow me to sum the absolute values? 

My example would be if we took the below rows as A1-A4, in excel if I were to write =sum(abs(a1:a4)) the result would be 6, similarly I could use = sumproduct(abs(a1:a4)) and return 6, neither of these are able to be applied in Workiva.

A1 = 1

A2 = 2

A3 = -1

A4 = -2

0

Opmerkingen

7 opmerkingen

Datum Stemmen
  • Isabel Messore

    Howdy Lee McCarthy, 

    I believe what you're looking for is the SUMPRODUCT function. Please give this a try and let me know how it goes! 

    =(ABS(SUMPRODUCT(A1:A4)))

    0
  • Lee McCarthy

    Hi Isabel, that unfortunately gives me the absolute value of the sum but not the sum of the absolute values. So instead of my desired result of 6 in my example it returns 0.

    Thanks

    0
  • Isabel Messore

    Let's try this Lee McCarthy! I just ran =SUMPRODUCT(ABS(A1:A4)) in WK (sandbox), and got 6.

     
     
     
    0
  • Lee McCarthy

    Ah, that is very interesting - when I try the same thing I get #VALUE! error with a helper message in the formula bar of 'Range references are not valid in single value context'. Are there settings which could be causing the differences in behaviour?

    0
  • Isabel Messore

    Hmmm.... Typically that message shows up when the input or argument in a formula or function is designed to handle only one piece of data. And that's not really the case here.  I'd want to check the formula for typos.

     
    A test would be for you to open a new sheet, and enter the data presented above, and then put =SUMPRODUCT(ABS(A1:A4)) (cut and paste) in B1 and see if that generates 6 Lee McCarthy1
    0
  • Lee McCarthy

    Hey Isabel, thanks for that - please see the attached picture which shows this test case in a new spreadsheet not working. Seems there is something different between our settings/setup?

    0
  • Isabel Messore

    Very interesting Lee McCarthy indeed.

    I'm going to create a Support ticket for this issue, as I'm unable to touch the files myself. Support will take a deeper dive into the settings and see what's going on. We appreciate your patience on this and one of our Support members will be in touch soon, hang tight!

    0

U moet u aanmelden om een opmerking te plaatsen.