Mar 06 2020 07:27 PM
I have a spreadsheet with 2500 rows of 10 randomly generated numbers (either 1, -1, 10, -10, 100 or -100). For every row, I want 3 functions. The first would calculate the absolute value of the sum of all the 1s and -1s. The second would do the same with the 10s and -10s and then divide by 10, and the third would do the same for the 100s and -100s and then divide by 100.
I'm very new to Excel so I don't know if there's an easy way to do this that I'm just completely missing, so please help me if you can. Thank you.
Mar 06 2020 08:07 PM
Hi,
Please try these formulas:
1s and -1s
=SUMPRODUCT(IF(ABS(A1:J1)=1,ABS(A1:J1)))
10s and -10s and then divide by 10
=SUMPRODUCT(IF(ABS(A1:J1)=10,ABS(A1:J1)))/10
100s and -100s and then divide by 100
=SUMPRODUCT(IF(ABS(A1:J1)=100,ABS(A1:J1)))/100
Hope that helps
Mar 06 2020 08:59 PM
Your requirement is equivalent to the count of specific absolute numbers. Thus, the formula for the count of absolute 1's is:
=SUMPRODUCT(--(ABS($A1:$J1)=1))
For 10's, the formula will be:
=SUMPRODUCT(--(ABS($A1:$J1)=10))
For 100's, the formula will be:
=SUMPRODUCT(--(ABS($A1:$J1)=100))
See the results of the foregoing formulas in the attached file.
Mar 06 2020 09:16 PM
@Haytham Amairah @Twifoo Thank you, but this wasn't what I was looking for.
I meant that I want something like this:
-1 1 -1 1 1 would return the answer of 1 because -1 + 1 + (-1) + 1 + 1 = 1.
Mar 06 2020 09:43 PM
Solution
You can change the formulas as follows:
=SUMPRODUCT(IF(ABS(A1:J1)=1,A1:J1))
=SUMPRODUCT(IF(ABS(A1:J1)=10,A1:J1))/10
=SUMPRODUCT(IF(ABS(A1:J1)=100,A1:J1))/100
Mar 06 2020 09:50 PM
Thank you @Haytham Amairah !
Mar 06 2020 09:43 PM
Solution
You can change the formulas as follows:
=SUMPRODUCT(IF(ABS(A1:J1)=1,A1:J1))
=SUMPRODUCT(IF(ABS(A1:J1)=10,A1:J1))/10
=SUMPRODUCT(IF(ABS(A1:J1)=100,A1:J1))/100