Difference between revisions of "Documentation/How Tos/Using Names as a Reference to Formula Items"
(Created page with 'Who does not love a formula like this? =(E18*F25)/((AVERAGE(C23:C20)) + SUM(D18:D29)) Now, the math is fairly complex and just looking at it, you are not sure what it is ref…') |
|||
| Line 20: | Line 20: | ||
Then you can create formulas like this. | Then you can create formulas like this. | ||
| − | + | [[Image:Doc_HowTo_Rangenames.png|thumb|none|500px|Sheet with range names]] | |
Note that you cannot do <tt> =Ement+Admit </tt> because those names refer to ranges. You cannot do <tt> =Ement+Admit </tt> because you cannot do <tt> =B17:B20+E17:E20 </tt>. | Note that you cannot do <tt> =Ement+Admit </tt> because those names refer to ranges. You cannot do <tt> =Ement+Admit </tt> because you cannot do <tt> =B17:B20+E17:E20 </tt>. | ||
| Line 29: | Line 29: | ||
There is a quicker way to get all your rows and columns named. Let us assume you have got this data. | There is a quicker way to get all your rows and columns named. Let us assume you have got this data. | ||
| − | Doc_HowTo_columnnames | + | [[Image:Doc_HowTo_columnnames.png|thumb|none|500px|Example]] |
| Line 41: | Line 41: | ||
Now you can create totals. Note that when you are in a formula and you start to type a name, it will appear in the Formula field as "Prepaid_Taxes" does here. Press Enter to accept it so you do not have to type the whole name. | Now you can create totals. Note that when you are in a formula and you start to type a name, it will appear in the Formula field as "Prepaid_Taxes" does here. Press Enter to accept it so you do not have to type the whole name. | ||
| − | Doc_HowTo_typingnames | + | [[Image:Doc_HowTo_typingnames.png|thumb|none|500px|Typing formulas with names]] |
As mentioned before, you will probably want to make fields to just refer to the totals. Select the cell and choose '''Insert > Names > Define'''. Type the name, for example <tt> TotBusExp </tt> for the cell G3 that displays the total of the Business Expenses. Then click '''Add''', and click '''OK'''. | As mentioned before, you will probably want to make fields to just refer to the totals. Select the cell and choose '''Insert > Names > Define'''. Type the name, for example <tt> TotBusExp </tt> for the cell G3 that displays the total of the Business Expenses. Then click '''Add''', and click '''OK'''. | ||
| Line 48: | Line 48: | ||
And then you can do something like this for the formula. (Yes, you might do a SUM(range) for this but let us say that you have got all these amounts, Bus exp and Salary etc. all over on different sheets or farther apart, so you would have to do the + instead) | And then you can do something like this for the formula. (Yes, you might do a SUM(range) for this but let us say that you have got all these amounts, Bus exp and Salary etc. all over on different sheets or farther apart, so you would have to do the + instead) | ||
| − | Doc_HowTo_namesinformula | + | [[Image:Doc_HowTo_namesinformula.png|thumb|none|500px|Calculating with names in formulas]] |
and get a result. | and get a result. | ||
| − | Doc_HowTo_result | + | [[Image:Doc_HowTo_result.png|thumb|none|500px|Result of the calculation]] |
Revision as of 09:21, 1 September 2009
Who does not love a formula like this?
=(E18*F25)/((AVERAGE(C23:C20)) + SUM(D18:D29))
Now, the math is fairly complex and just looking at it, you are not sure what it is referring to.
If you used names, which let you give more clear, descriptive names to parts of your spreadsheet, it could look like this.
=(TotalSales*TotalMonths) / ((AVERAGE(2006Sales)) + SUM(SalesBonus))
That is a little clearer.
But how do you add the names? You can assign any name you want as follows:
- Select one or more cells and choose Insert > Names > Define.
- Type the name, click Add, and click OK.
Then you can create formulas like this.
Note that you cannot do =Ement+Admit because those names refer to ranges. You cannot do =Ement+Admit because you cannot do =B17:B20+E17:E20 .
You have to create more names to refer to just the totals. Create other names for the total fields, for example the =SUM(Ement) total field, so that then you can type =TotEment+TotAdmit (Total Entertainment Expenses plus Total Admission Expenses).
There is a quicker way to get all your rows and columns named. Let us assume you have got this data.
- Select all the data and choose Insert > Names > Create.
- In this case it makes sense to select the checkmarks for Top row and Left column since that's where the labels are.
- Then click OK.
To look at the names created, choose Insert > Names > Define.
Now you can create totals. Note that when you are in a formula and you start to type a name, it will appear in the Formula field as "Prepaid_Taxes" does here. Press Enter to accept it so you do not have to type the whole name.
As mentioned before, you will probably want to make fields to just refer to the totals. Select the cell and choose Insert > Names > Define. Type the name, for example TotBusExp for the cell G3 that displays the total of the Business Expenses. Then click Add, and click OK.
And then you can do something like this for the formula. (Yes, you might do a SUM(range) for this but let us say that you have got all these amounts, Bus exp and Salary etc. all over on different sheets or farther apart, so you would have to do the + instead)
and get a result.
This How To comes from Solveig Haugland's Blog
| Content on this page is licensed under the Public Documentation License (PDL). |
