0

I have a Google Sheet and I need to add a lot of stuff like

(ABS(A1)+A1)/A1+ (ABS(A2)+A2)/A2+...+ (ABS(A10)+A10)/A10

but I don't want to write out the sum each time for each term A1,A2,A3,..., A10

Is there a way to write the sum more concise? like

SUM( (|x| + x)/x : for x from A1 to A10) 

I know that's not correct but that is what I want.

1
  • Have you tried grabbing the square corner of the cell containing the formula and dragging it down the column (for example). If certain cell refs are fixed, use the $ sign to make the cell reference absolute (such as $A$1) otherwise the cell ref will increment as you drag it down. – 24601 Jan 8 at 19:58
1

You can use an Arrayformula to achieve your goal.

You don't need to drag down a formula nor specify cells. This will work for every correct calculation in column A:

=Sum(Arrayformula(Iferror((Abs(A2:A8)+A2:A8)/A2:A8,"")))

enter image description here

or ignore iferror if you are sure A2:A8 contains numbers (and not text or blank elements):

=Sum(Arrayformula((Abs(A2:A8)+A2:A8)/A2:A8))

4
  • I'm sorry, could you explain a little bit what each of the items mean? For example, I do not want to sum over the entirety of column A, suppose I only want to sum from A2 to A8 specifically – Christopher Rose Jan 11 at 13:25
  • Abs(A1:A)+A1:A)/A1:A is your current solution. The difference is that instead of applying it to just a single element you can apply it to a range of elements A1:A. To apply it to a range of elements you need to use arrayformula. You don't want to apply it for every cell in column A but only for the ones which have a value (non blank value). To do that you can use iferror to prevent empty cells from being used by the formula. @ChristopherRose – soMario Jan 11 at 13:27
  • I'm sorry, but I do not understand. For example, the solution I want should not be a matrix, but should be a single number. Also, for the A column you wrote in your example, the sum I want should be 3 but your sum has 2 three times (or is it 0?) -- I don't understand – Christopher Rose Jan 11 at 13:30
  • @ChristopherRose I updated my answer. Check it again. – soMario Jan 11 at 13:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.