I'm not sure I can explain it any better than I already have:
The formula tests the column numbers to see if that particular column should
be included in the sum.
F3 = column() = 6
G3 = column() = 7
H3 = column() = 8
I3 = column() = 9
Using the MOD function we then need to find a divisor that returns a
specific value and this establishes a pattern that we can take advantage of.
MOD(COLUMN(F3),3) = 0
MOD(COLUMN(G3),3) = 1
MOD(COLUMN(H3),3) = 2
MOD(COLUMN(I3),3) = 0
So, we're telling the formula to sum those columns where the column number
returns a MOD of 0 when the divisor is 3.
The cell interval really doesn't have anything to do with it. Every other
cell, every 3rd cell, every 10th cell. It's the actual range that you need
to know, specifically, the column (or row) numbers.
What you need to do is compare the MOD of the column (or row) numbers and
experiment with the divisor until you find a good pattern. Sometimes it's
not so easy!
The first formula started in column E (5) and the second formula started in
column F (6) so a MOD with the same divisor would be different.
About the "--", see:
http://mcgimpsey.com/excel/formulae/doubleneg.html
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Biff
"bthieson" wrote in
message ...
Okay it works, but now I need to understand why. In the first formula we
used, the mod() had to equal 2, and in the second it had to equal 0. Now
I had a formula that I was trying before and I was always using equal to
0, why would it be 2 in the case of the first formula? I assumed that
because I was looking for every third column starting at the point I
did, that would mean I would want to mod() 3 and search for a 0. If you
wouldn't mind explaining the 2 to me, I would definately appreciate it.
The other thing I didn't understand in the formula, was the (--)? What
does that do. In my initial formula, I was using an if statement where
you had the (--). What is it?
-Ben
--
bthieson
------------------------------------------------------------------------
bthieson's Profile:
http://www.excelforum.com/member.php...o&userid=34357
View this thread: http://www.excelforum.com/showthread...hreadid=541295