Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Array Formula Not Working with Range with Formulas | Excel Discussion (Misc queries) | |||
Problem with Vlookup array selection | Excel Worksheet Functions | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Excel array formulas | Excel Discussion (Misc queries) | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |