B
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.
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.