quirky array not working
 

I have three separate 15 cell blocks of cells. I need to do a
SUM(A/(1+B)*C), where each letter is the blocks of cells. Problem is
two-fold. First, while A and B are contiguous blocks, C is not (it's all one
column, but each cell has three others in between them). So the way I was/am
trying to write C into the formula was by doing, in parentheses, each cell
separated by commas, figuring that creates the 15 cell block I need. So one
question is whether that's the right way to indicate in an array formula
(shft+ctrl+enter, is what I mean by array in this case, in case there is any
other definition). Or is there a function name of some sort that indicates
to the array the "this is the block you need to evaluate as a contiguous set"?

Second, the starting value of the C cells, if I can avoid having to change
this, is the word "discount" (it is a percentage formatted cell, but I need
it to say this word to instruct the person on what it is). I figured that
because it is being used as a straight multiplicative variable, I needed to
make sure it was a number, so in place of "C", I tried using an
"if(isnumber(C written as previously mentioned in a cell-comma style for all
15 cells),(C written as 15 cells),1)". I was hoping the way it would be
evaluated by the array would be to take each of A and B and do the IF on each
of C, and then put that result into the SUM.

Anyway, it's giving me a VALUE answer, and I don't know if it's because of
the disjointed C cells or the attempt at IF or what. I know that when I
create a separate little area on the sheet with a contiguous block of cells
that just references each of the C cells, I can make that contiguous block
work properly in the formula. But since I am all about trying to be
efficient, and have been marveling at arrays since I first figured them out,
I figured I'd give this one to the experts to see if I am missing something
that could help me do this calc in a self-contained way.

Thanks for any insight.

--
Boris

quirky array not working
 

How about this

=SUM(A1:A15)/(1+SUM(B1:B15))*SUMPRODUCT(--(MOD(ROW(C1:C60),4)=1),C1:C60)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"BorisS" wrote in message
...
I have three separate 15 cell blocks of cells. I need to do a
SUM(A/(1+B)*C), where each letter is the blocks of cells. Problem is
two-fold. First, while A and B are contiguous blocks, C is not (it's all
one
column, but each cell has three others in between them). So the way I
was/am
trying to write C into the formula was by doing, in parentheses, each cell
separated by commas, figuring that creates the 15 cell block I need. So
one
question is whether that's the right way to indicate in an array formula
(shft+ctrl+enter, is what I mean by array in this case, in case there is
any
other definition). Or is there a function name of some sort that
indicates
to the array the "this is the block you need to evaluate as a contiguous
set"?

Second, the starting value of the C cells, if I can avoid having to change
this, is the word "discount" (it is a percentage formatted cell, but I
need
it to say this word to instruct the person on what it is). I figured that
because it is being used as a straight multiplicative variable, I needed
to
make sure it was a number, so in place of "C", I tried using an
"if(isnumber(C written as previously mentioned in a cell-comma style for
all
15 cells),(C written as 15 cells),1)". I was hoping the way it would be
evaluated by the array would be to take each of A and B and do the IF on
each
of C, and then put that result into the SUM.

Anyway, it's giving me a VALUE answer, and I don't know if it's because of
the disjointed C cells or the attempt at IF or what. I know that when I
create a separate little area on the sheet with a contiguous block of
cells
that just references each of the C cells, I can make that contiguous block
work properly in the formula. But since I am all about trying to be
efficient, and have been marveling at arrays since I first figured them
out,
I figured I'd give this one to the experts to see if I am missing
something
that could help me do this calc in a self-contained way.

Thanks for any insight.

--
Boris

quirky array not working
 

Interesting. For my education, what is it doing? Can you put the sumproduct
into lay terms. Not following it completely. Thanks.
--
Boris


"Bob Phillips" wrote:

How about this

=SUM(A1:A15)/(1+SUM(B1:B15))*SUMPRODUCT(--(MOD(ROW(C1:C60),4)=1),C1:C60)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"BorisS" wrote in message
...
I have three separate 15 cell blocks of cells. I need to do a
SUM(A/(1+B)*C), where each letter is the blocks of cells. Problem is
two-fold. First, while A and B are contiguous blocks, C is not (it's all
one
column, but each cell has three others in between them). So the way I
was/am
trying to write C into the formula was by doing, in parentheses, each cell
separated by commas, figuring that creates the 15 cell block I need. So
one
question is whether that's the right way to indicate in an array formula
(shft+ctrl+enter, is what I mean by array in this case, in case there is
any
other definition). Or is there a function name of some sort that
indicates
to the array the "this is the block you need to evaluate as a contiguous
set"?

Second, the starting value of the C cells, if I can avoid having to change
this, is the word "discount" (it is a percentage formatted cell, but I
need
it to say this word to instruct the person on what it is). I figured that
because it is being used as a straight multiplicative variable, I needed
to
make sure it was a number, so in place of "C", I tried using an
"if(isnumber(C written as previously mentioned in a cell-comma style for
all
15 cells),(C written as 15 cells),1)". I was hoping the way it would be
evaluated by the array would be to take each of A and B and do the IF on
each
of C, and then put that result into the SUM.

Anyway, it's giving me a VALUE answer, and I don't know if it's because of
the disjointed C cells or the attempt at IF or what. I know that when I
create a separate little area on the sheet with a contiguous block of
cells
that just references each of the C cells, I can make that contiguous block
work properly in the formula. But since I am all about trying to be
efficient, and have been marveling at arrays since I first figured them
out,
I figured I'd give this one to the experts to see if I am missing
something
that could help me do this calc in a self-contained way.

Thanks for any insight.

--
Boris

quirky array not working
 

The SUMPRODUCT looks at each cell in C1:C60, and by using the MOD function,
retains row 1, 5, 9, etc. Where the row number MOD 4 is 1, then it picks up
that value in it's summing. The MOD part is a conditional test, which
returns an array of TRUE/FALSE. The -- changes that to an array of 1/0,
which is then used in the product with the C1:C60 array of values.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"BorisS" wrote in message
...
Interesting. For my education, what is it doing? Can you put the
sumproduct
into lay terms. Not following it completely. Thanks.
--
Boris


"Bob Phillips" wrote:

How about this

=SUM(A1:A15)/(1+SUM(B1:B15))*SUMPRODUCT(--(MOD(ROW(C1:C60),4)=1),C1:C60)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"BorisS" wrote in message
...
I have three separate 15 cell blocks of cells. I need to do a
SUM(A/(1+B)*C), where each letter is the blocks of cells. Problem is
two-fold. First, while A and B are contiguous blocks, C is not (it's
all
one
column, but each cell has three others in between them). So the way I
was/am
trying to write C into the formula was by doing, in parentheses, each
cell
separated by commas, figuring that creates the 15 cell block I need.
So
one
question is whether that's the right way to indicate in an array
formula
(shft+ctrl+enter, is what I mean by array in this case, in case there
is
any
other definition). Or is there a function name of some sort that
indicates
to the array the "this is the block you need to evaluate as a
contiguous
set"?

Second, the starting value of the C cells, if I can avoid having to
change
this, is the word "discount" (it is a percentage formatted cell, but I
need
it to say this word to instruct the person on what it is). I figured
that
because it is being used as a straight multiplicative variable, I
needed
to
make sure it was a number, so in place of "C", I tried using an
"if(isnumber(C written as previously mentioned in a cell-comma style
for
all
15 cells),(C written as 15 cells),1)". I was hoping the way it would
be
evaluated by the array would be to take each of A and B and do the IF
on
each
of C, and then put that result into the SUM.

Anyway, it's giving me a VALUE answer, and I don't know if it's
because of
the disjointed C cells or the attempt at IF or what. I know that when
I
create a separate little area on the sheet with a contiguous block of
cells
that just references each of the C cells, I can make that contiguous
block
work properly in the formula. But since I am all about trying to be
efficient, and have been marveling at arrays since I first figured
them
out,
I figured I'd give this one to the experts to see if I am missing
something
that could help me do this calc in a self-contained way.

Thanks for any insight.

--
Boris