Making a new Excel Formula?

[GOM3RPLY3R]

Hello all,

I am working on a school project in which I have created a formula to calculate a score for the performance of a graphics card. It is as follows:

p= peformance
t=temperature
c=clock

I want to input this into excel and just use it as a shortcut instead of having to do: =(((K7*(10))+(G7*(10)))/2)+K7-(1150*(0.1)) :and re-inputting the columns every time. I'll have to use this for about 40 or 50 more excel documents, so any help would greatly be appreciated. ^_^

 

[PepitoTV]

google for "excel custom function"

 

[13thmonkey]

just copy and paste the formula?

 

[P1nnacle]

First do: File>Options>Customize Ribbon
Find the Developer box and check it. Close out of the options window.
Click on the developer tab
Click Visual Basic
On "This Workbook" right click
Insert>Module

in the window that pops up type:

Function GPUPerformance (p, t, c)
GPUPerformance = (((p * 10) + (t * 10)) / 2) + t - (c * 0.01)
End Function

Save As> Excel Macro-Enabled Workbook
Now, in your workbook if you type in =GPUPerformance(p, t, c) (where p, t, and c are your values) you will get the calculation in the cell you selected.

Note that you can change GPUPerformance to any name as long as you update all of them, (i.e. changing it to GPU so it's shorter).

 

[spdragoo]

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.

 

[P1nnacle]

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.

 

[spdragoo]

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.