Who doesn't love a formula like this?
=(E18*F25)/((AVERAGE(C23:C20)) + SUM(D18:D29))
Now, the math is fairly complex and I just made it up to look extra complex, but just looking at it, you're not sure what it's 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))
Ahh.....that's a little clearer. (Since it's a random made-up example, it might not make total sense from a business standpoint, but you're at least in the ballpark now.)
That's all very well, but how to do you add the names?
Then you can create formulas like this.
Note that you can't do this:
because those names refer to ranges. You can't do =Ement+Admit because you can't do =B17:B20+E17:E20
You'd need to create more names to refer to just the totals. You'd create another name for the total field where the =SUM(Ement) total is, so that then you can type =TotEment+TotAdmit (total Entertainment Expenses plus Total Admission Expenses).
There's a quicker way to get all your columns named. Let's say you've 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.
Now you can create totals, for instance, like this. Note that when you're 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 don't have to type the whole name.
As mentioned before, you'll probably want to make fields to just refer to the totals. Select the cell and choose Insert > Names > Define. Type the name, 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's say that you've 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.
Tip: If you want some visual help figuring out where all those names come from, now that you don't have the cell references, you can choose Tools > Detective > Trace Precedents.