Hello all,

I have a question for the Excel gurus which read these groups. I have had
a look through the archives and as far as I can tell, there is no way to
do what I want so I thought I'd ask here.

Take the simple case of calculating forecast sales; if the market for widgets
is x million per year and your forecast market share in that year is y%,
then forecast sales for a given year = x * y

Obviously it is possible to set up, for example, a named cell "market_share"
and another "market_size" and then write the formula in a third cell for
sales which has the formula
"=market_share*market_size" to calculate sales. This is clearly a very
readable formula which someone else looking at the worksheet would easily
understand. The drawback of this is that it would only work for one year.
Changing the names (i.e. to add the year,so that market_share for 2005 would
be market_share_2005) would be unworkable for any large sheet (and you could
not drag the formulas across into a new column to add another year).

Is there some way to make named ranges behave like arrays in programming
languages? It would be great to have a series of named columns (i.e. 2004,
2005, 2006) and then the formula could look like:
sales(year) = market_share(year) * market_size(year). The formulas would
be human readable and the formulas should still be able to be copied to new
columns.

As far as I know this is impossible but I'd be happy to hear any suggestions
on how this could be done.

Cheers,
Shane

Notwithstanding the use of Functions and the ability to name a Function
with any useful name, I think the 'readable' portion is usually covered
by correctly naming the columns and using Freeze Panes, thus column A
would be headed Market Share and column B would be headed Market Size,
and the formula A5 * B5 would be recognised as Market Share * Market
Size

Each year might be on following rows, or in suitably named other
columns, with the same result, but hey, I'm not a guru by any means,
hopefully one will provide a better answer.

--

Shane Henderson Wrote:
Hello all,

I have a question for the Excel gurus which read these groups. I have
had
a look through the archives and as far as I can tell, there is no way
to
do what I want so I thought I'd ask here.

Take the simple case of calculating forecast sales; if the market for
widgets
is x million per year and your forecast market share in that year is
y%,
then forecast sales for a given year = x * y

Obviously it is possible to set up, for example, a named cell
"market_share"
and another "market_size" and then write the formula in a third cell
for
sales which has the formula
"=market_share*market_size" to calculate sales. This is clearly a
very
readable formula which someone else looking at the worksheet would
easily
understand. The drawback of this is that it would only work for one
year.
Changing the names (i.e. to add the year,so that market_share for 2005
would
be market_share_2005) would be unworkable for any large sheet (and you
could
not drag the formulas across into a new column to add another year).

Is there some way to make named ranges behave like arrays in
programming
languages? It would be great to have a series of named columns (i.e.
2004,
2005, 2006) and then the formula could look like:
sales(year) = market_share(year) * market_size(year). The formulas
would
be human readable and the formulas should still be able to be copied to
new
columns.

As far as I know this is impossible but I'd be happy to hear any
suggestions
on how this could be done.

Cheers,
Shane


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=530767

If I had a named range JMB2005 and JMB2006

E1 = 2005
F1 = 2006

in E2 enter
=INDIRECT("JMB"&E1)

then copy to F1



"Shane Henderson" wrote:

Hello all,

I have a question for the Excel gurus which read these groups. I have had
a look through the archives and as far as I can tell, there is no way to
do what I want so I thought I'd ask here.

Take the simple case of calculating forecast sales; if the market for widgets
is x million per year and your forecast market share in that year is y%,
then forecast sales for a given year = x * y

Obviously it is possible to set up, for example, a named cell "market_share"
and another "market_size" and then write the formula in a third cell for
sales which has the formula
"=market_share*market_size" to calculate sales. This is clearly a very
readable formula which someone else looking at the worksheet would easily
understand. The drawback of this is that it would only work for one year.
Changing the names (i.e. to add the year,so that market_share for 2005 would
be market_share_2005) would be unworkable for any large sheet (and you could
not drag the formulas across into a new column to add another year).

Is there some way to make named ranges behave like arrays in programming
languages? It would be great to have a series of named columns (i.e. 2004,
2005, 2006) and then the formula could look like:
sales(year) = market_share(year) * market_size(year). The formulas would
be human readable and the formulas should still be able to be copied to new
columns.

As far as I know this is impossible but I'd be happy to hear any suggestions
on how this could be done.

Cheers,
Shane