Named Formulas
Define names can be used to define ranges, dynamic ranges, constants, and even formulas. To define a formula as a define name, choose Insert>Name>Define from the menu. Type your formula in the Refers To box and away you go. Here’s a defined name that doubles the value of the cell to the left.
How does A1 refer to the cell to the left? I’m glad you asked. The tricky part about defined formulas is the cell references. You almost always want them to be relative (no $) otherwise what the heck good would they be. To get the above defined formula to always refer to the cell to the left, I selected cell B1 when I defined it.
By making the A1 reference relative, it will adjust when I use the name in other cells. In D5, it will double C5, for example. Note that in A8, it will double IV8 - don’t ask me why it just is.
I don’t use defined formulas very often, but they do come in handy when you need to get your head around a complex formula. As an example, you can shorten this formula
=IF(ISNA(MATCH(C1,$A$1:$A$5,FALSE)),”No Match”,MATCH(C1,$A$1:$A$5,FALSE))
to this
=IF(NoMatch,”No Match”,MATCH(C1,A1:A5,FALSE))
by defining a name like
Words of wisdom I received one day when I was using many formulas in Names:
“I believe a defined Name is only calculated whenever a cell that directly refers to it is calculated, but if you have multiple cells referring directly to a Name, and you recalculate all these multiple cells, then the Name will be recalculated multiple times. So defined Names do not work exactly like cells: it’s more like the Name gets substituted by its RefersTo whenever a formula containing the Name gets calculated. So in general it’s is not efficient to put calculation-intensive functions inside Names: use a spare cell somewhere instead, and if you want to use Names then give the spare cell a name.
Charles Williams
http://www.DecisionModels.com”
Jamie.
–
What about boolean gates? I was somewhat surprised they didn’t get a mention. A formula along the lines of:
=(A1=”One”)*1+(A1=”Two”)*2+(A1=”Three”)*3+(A1=”Four”)*4+(A1=”Five”)*5+(A1=”Six”)*6+(A1=”Seven”)*7+(A1=”Eight”)*8+(A1=”Nine”)*9+(A1=”Ten”)*10
…provides the ability to test beyond the nesting limit. I’m not suggesting for a moment that it’s pretty (I would normally use the method you suggested of a VLOOKUP table), but it works. And (IMHO) it’s a lot easier to follow than nested IFs as the logic is sequential instead of jumping all over the place, spaghetti code style.
Just be surfing around in net. I definitely fpund a very informal place with a lot of good stuff for everybody. I will
certainly visit your site again sometime. Really good work.
Great information here. Just what I needed to complete my essay. Thanks. In my opinion the internet is the key to success!