formulas 1

[ollylandandwater]

Is there a way of saving formulas that I use regularly in excel? Such as,

=A2&","&B2

=SQRT(((B2-B5)^2)+((C2-C5)^2))

So I don’t have to retype them, as at the moment i have them saved in word, then i copy them into excel and then edit the cell number when i need to use them.

 

[francesca]

Create functions in your personal macro book and refer to them.

 

[joerd]

Why use Word when you could use Excel just as well for this purpose? You could save a template with your library of formulas if you want to.
Otherwise, there are a lot of "clipbook" type programs out there, which will allow you to keep a library of commonly typed character sequences. Google for it and thou shall find.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[yogia]

Hi ollylandandwater:

If I understand you correctly ...

type the apostrophe character ' before the = sign in the formula, that will make the formula a TEXT entry

then you can copy the TEXT entry where you need to, delete the apostrophe key and the TEXT entry will be back to EXCEL formula.


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[IFRs]

If you want the cell references to change based on the location of the cursor when you press "go" or "insert" only a macro or VB can do what you want. I think you want your first example to really be "current cell" & "," & "the next cell to the right". This sounds like a great example of the power of VB.

 

[cummings54]

In Excel, you can use a formula name to save a complex formula and recall it. You start by creating a range name but instead of a range reference in the refers to: box enter your formula:

Use the Insert:Nameefine menu to open the Define Name dialog. In the Names In workbook: box enter your name, say Magnitude then in the Refers to: box enter the formula =SQRT(((B2-B5)^2)+((C2-C5)^2)). Since the formula was entered as a relitive reference (ie. without the $ in the address) the formula will change depending on where it is entered on the worksheet. If the formula must be the same anywhere it is used ensure the formula is entered with absolute references.

to use the formula name type =Magnitude where you need it.

 

[NormPeterson]

Star for that.

With a caveat - if you later label a range with the same name, you will #VALUE any cells that invoked that user-named function (can be undone, though).


Norm

 

[yakpol]

Using named ranges with relative references is very dangerous! It may work for excercising the technique but not for real use. Copy - paste (including all reference cells) will be the best way to reuse stored formulas.