|
named ranges
Good Afternoon:
I am taking an Excel course and this is one of my assignments. I have used Excel for years but never used named ranges. I have checked online and know how to select a range and name it, but in this assignment I don't know which cells to select to name. Can someone help? Assignment ... Create a workbook consisting of 2 worksheets. Name the first worksheet tab Assumptions and the second worksheet P&L. The Assumptions worksheet ... A B C D E F 1 2002 2003 2004 2005 2006 2 Revenue Growth (%) 2% 4% 3% 5% 3 Expense Growth (%) 3% 3% 2% 5% 4 Tax Rate (%) 16% 15% 12% 13% The P&L worksheet ... A B C D E F 1 2002 2003 2004 2005 2006 2 Revenue $1,000 3 Expenses $550 4 Gross Profit 5 Taxes $180 6 Net Profit Create names for data ranges in both worksheets, using row the labels (hint: select rows and their labels, then Insert * Name * Create). In the Assumptions worksheet, add values: values for 2002 can be left blank; assumptions for other years can be any reasonable values you want (e.g. 2%, 10%, 40%). In the P&L worksheet, use numeric values for year 2002 as shown. Then, enter formulas as follows: Revenue: previous years revenue multiplied by current years revenue growth. Expenses: previous years expenses multiplied by current years expense growth (analogous to Revenue formula). Gross Profit: Revenue minus Expenses. Taxes: Gross Profit multiplied by Tax Rate. Net Profit: Gross Profit minus Taxes. Hints: Check that Excel is set to Accept labels in formulas found in Tools * Options Try writing the formula for Revenue in the year 2003 as = Revenue 2002*(1+Revenue_Growth), leaving a space between Revenue and 2002 and Excel should do the right thing. You can then copy this formula by clicking the fill handle and dragging to remaining cells in the row and Excel will automatically adjust the formula for the correct year. THANK YOU! |
named ranges
Carol Ann wrote:
Good Afternoon: I am taking an Excel course and this is one of my assignments. I have used Excel for years but never used named ranges. I have checked online and know how to select a range and name it, but in this assignment I don't know which cells to select to name. Can someone help? Assignment ... Create a workbook consisting of 2 worksheets. Name the first worksheet tab Assumptions and the second worksheet P&L. The Assumptions worksheet ... A B C D E F 1 2002 2003 2004 2005 2006 2 Revenue Growth (%) 2% 4% 3% 5% 3 Expense Growth (%) 3% 3% 2% 5% 4 Tax Rate (%) 16% 15% 12% 13% . . . Create names for data ranges in both worksheets, using row the labels (hint: select rows and their labels, then Insert * Name * Create). E.g., Select $A$2:$F$2, then Insert|Name|Create and pick a name. Do you need more? Alan Beban |
named ranges
Afraid that I already knew to do that. I'm supposed to use the formula
"=Revenues 2002*(1+Revenue_Growth)" to show the increase from 2002 to 2003. Each time I try I get "the formula you typed contains an error". "Alan Beban" wrote: Carol Ann wrote: Good Afternoon: I am taking an Excel course and this is one of my assignments. I have used Excel for years but never used named ranges. I have checked online and know how to select a range and name it, but in this assignment I don't know which cells to select to name. Can someone help? Assignment ... Create a workbook consisting of 2 worksheets. Name the first worksheet tab Assumptions and the second worksheet P&L. The Assumptions worksheet ... A B C D E F 1 2002 2003 2004 2005 2006 2 Revenue Growth (%) 2% 4% 3% 5% 3 Expense Growth (%) 3% 3% 2% 5% 4 Tax Rate (%) 16% 15% 12% 13% . . . Create names for data ranges in both worksheets, using row the labels (hint: select rows and their labels, then Insert * Name * Create). E.g., Select $A$2:$F$2, then Insert|Name|Create and pick a name. Do you need more? Alan Beban |
named ranges
Carol Ann wrote:
Afraid that I already knew to do that. I'm supposed to use the formula "=Revenues 2002*(1+Revenue_Growth)" to show the increase from 2002 to 2003. Each time I try I get "the formula you typed contains an error". Sorry; I thought I was responding to "but in this assignment I don't know which cells to select to name. Can someone help?" Alan Beban |
named ranges
You can't have a space in a name, so Excel has probably named the
range "Revenues_2002" (depends on how you defined it), and this does not tie in with what you have typed in your formula. Hope this helps. Pete On Feb 4, 2:08 am, Carol Ann wrote: Afraid that I already knew to do that. I'm supposed to use the formula "=Revenues 2002*(1+Revenue_Growth)" to show the increase from 2002 to 2003. Each time I try I get "the formula you typed contains an error". "Alan Beban" wrote: Carol Ann wrote: Good Afternoon: I am taking an Excel course and this is one of my assignments. I have used Excel for years but never used named ranges. I have checked online and know how to select a range and name it, but in this assignment I don't know which cells to select to name. Can someone help? Assignment ... Create a workbook consisting of 2 worksheets. Name the first worksheet tab "Assumptions" and the second worksheet "P&L." The "Assumptions" worksheet ... A B C D E F 1 2002 2003 2004 2005 2006 2 Revenue Growth (%) 2% 4% 3% 5% 3 Expense Growth (%) 3% 3% 2% 5% 4 Tax Rate (%) 16% 15% 12% 13% . . . * Create names for data ranges in both worksheets, using row the labels (hint: select rows and their labels, then Insert Name Create...). E.g., Select $A$2:$F$2, then Insert|Name|Create and pick a name. Do you need more? Alan Beban- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 12:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com