P1nnacle said:

spdragoo said:

There are a couple of ways to do it, but they do have their downsides:

1. Enter the formula into a blank spreadsheet, then save the changes into the "Normal" template that Excel uses for a blank workbook.

-- Pro: you will have it available in every blank Excel spreadsheet you create from now on.

-- Con: the formula will be in *every* new Excel workbook, even if you don't need it. Plus, you will need to probably adjust the formula in a new workbook, which can take almost as much time as typing it from scratch.

2. Write it as a custom function (i.e. like the SUM() function).

-- Pros: Saves on typing, because you're only using the function name & the parameters (either cell references or fixed numbers) as you need them. Excel can be set to have the function available without actually storing the function in every single file.

-- Cons: Requires VBA (which may mean learning to use it). Requires saving the function in a special add-in file (*.xla), which then has to be saved in the "XLStart" folder on your hard drive.

All versions of Excel come with a light end VBA that you can use to make a macro, you don't need additional software. The integration also means it's not hard to use the function in Excel. The only challenge is making it accessible, the function writing itself (as seen above) is relatively simple.

Didn't say you needed to load VBA, I said you'll need to know how to use it. Someone who doesn't have a programming background will need to start at the "VBA for Dummies" level before they can begin to write a custom function, & even someone who does come from a programming background (& hasn't used VBA before) will probably want to at least get a reference for VBA so that they can write the function.

And if he's going to be using the function in that many workbooks, he'll have to a) keep the original workbook open every time he wants to use the function, b) save the original workbook as a template to use every single time he wants to have it available in a stand-alone workbook, or c) have it saved in an add-in file that Excel loads whenever he opens it so that he can use the function. None of these options involving installing extra software; they're the equivalent of editing startup or .ini files to modify an existing program's operation.

Of course, he could just save a simple text file with the formula typed up, & then cut, paste & edit it into every single workbook he wants to use it in. That would be a lot simpler tech-wise than using VBA, but might not save him any time.