LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
BorisS
 
Posts: n/a
Default 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
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Array Formula Not Working with Range with Formulas [email protected] Excel Discussion (Misc queries) 4 February 1st 06 02:01 PM
Problem with Vlookup array selection Scott269 Excel Worksheet Functions 2 January 30th 06 05:29 PM
Positioning Numeric Values Resulting from 6 Column Array Formula Sam via OfficeKB.com Excel Worksheet Functions 2 January 5th 06 02:03 AM
Excel array formulas Les Gordon Excel Discussion (Misc queries) 1 September 3rd 05 04:12 PM
VBA Import of text file & Array parsing of that data Dennis Excel Discussion (Misc queries) 4 November 28th 04 10:20 PM


All times are GMT +1. The time now is 04:36 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"