Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
Named range question
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named range question
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 |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
Named range question
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating a named range | Excel Worksheet Functions | |||
Range matching multiple named Ranges | Excel Discussion (Misc queries) | |||
Array to named range conversion... | Excel Discussion (Misc queries) | |||
copy named range to powerpoint | Excel Worksheet Functions | |||
Can I use named range in data range box when creating pie chart? | Charts and Charting in Excel |