Adding in Excel
Thank you very much Sloth. That helps alot. I'm going to have to experiment
with it to fully capture the concept. Thanks again.
--
Sincerely, Michael Colvin
"Sloth" wrote:
SUMPRODUCT multiplies values from two arrays
MOD take the remainder of the division
ROW give the row of the cell
Broken Down...
ROW(A1:A10) - {1,2,3,4,5,6,7,8,9,10} *NOTE:{} signifies an array
MOD(ROW(A1:A10),2) - {1,0,1,0,1,0,1,0,1,0}
SUMPRODUCT(~~~) - {1,0,1,0,1,0,1,0,1,0}*{A1,A2,A3,A4,A5,A6,A7,A8,A9, A10}
this becomes...
1*A1+0*A2+1*A3+0*A4+...
the second formula uses 1- and becomes
0*A1+1*A2+0*A3+1*A4+...
Hope this helps
"Michael" wrote:
Hi Sloth. If it's not too much trouble, would you mind explaining how that
formula works. I'm afraid I'm struggling with the mod and sumproduct
functions. Thanks in advance.
--
Sincerely, Michael Colvin
"Sloth" wrote:
The easiest method would be
=A1+A3+A5+A7+A9
=A2+A4+A6+A8+A10
You can also use something like
=SUMPRODUCT(MOD(ROW(A1:A10),2),A1:A10)
=SUMPRODUCT(1-MOD(ROW(A1:A10),2,A1:A10)
This way you can just change the range values if you want to expand the
range without specifying the specific cells.
"Ron" wrote:
I have a time sheet. One row has the total number of Regular hours worked in
7 days. The next row has the total number of Overtime hours worked in those 7
days, the next row regular hours, the next overtime hours, and so on for 5
rows down. At the bottom of the sheet I need to add only the Regular hours in
one cell and I need the total Overtime hours in another cell. So I need a
formula that will add every other row, or every other number in the same
column.
|