quirky array not working

[BorisS]

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

[Bob Phillips]

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" <BorisS@discussions.microsoft.com> wrote in message
news:C50C6EAD-40AA-4F73-8214-A02D4B4D3412@microsoft.com...
> 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

[BorisS]

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" <BorisS@discussions.microsoft.com> wrote in message
> news:C50C6EAD-40AA-4F73-8214-A02D4B4D3412@microsoft.com...
> > 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
>
>
>

[Bob Phillips]

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" <BorisS@discussions.microsoft.com> wrote in message
news:06556B33-26C8-4377-A289-97F4B688E6FB@microsoft.com...
> 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" <BorisS@discussions.microsoft.com> wrote in message
> > news:C50C6EAD-40AA-4F73-8214-A02D4B4D3412@microsoft.com...
> > > 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
> >
> >
> >