Function for % gain?

  • Thread starter Thread starter aheitner
  • Start date Start date
A

aheitner

Is there a built-in function that computes % gain?

Say, A1=100 B1=150, and I want C1 to compute ((B1-A1)/A1). For years
I've been just entering the formula, but it seems that there should b
a function that does it for me. If not, how can I create a custo
function to do it?

Thanks,

Andre
 
Hi Andrew!

Function Increase(BaseNum As Double, NewNum As Double) As Double
Increase = (NewNum - BaseNum) / BaseNum
End Function

Select the above and Copy
In Excel
Right Click a Sheet Tab
Select the workbook name in the top left window
Insert > Module
Paste [into the code window on the right]
Back to Excel by clicking the Excel icon on left of the toolbar
Test

=Increase(A1,B1)


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Norman,


Select the above and Copy
In Excel
Right Click a Sheet Tab
Select the workbook name in the top left window
Insert > Module

So I can't get to this step...is it possible I don't have the "module
function installed? I right click on Sheet, hit Insert, and it give
me a bunch of options to insert: worksheet, chart, macro, etc. but n
"Module". Any other way to insert a module?

Many thanks.

-Andre
 
Hi
Norman forgot a step :-)
Select the above and Copy
In Excel
Right Click a Sheet Tab
---
choose 'Code' -> thios will open the VBA editor
---
Select the workbook name in the top left window
Insert > Module
Paste [into the code window on the right]
Back to Excel by clicking the Excel icon on left of the toolbar
Test
 
Hi Andrew!

Revised for missing line:

Function Increase(BaseNum As Double, NewNum As Double) As Double
Increase = (NewNum - BaseNum) / BaseNum
End Function

Select the above and Copy
In Excel
Right Click a Sheet Tab
Select "View Code"
Select the workbook name in the top left window
Insert > Module
Paste [into the code window on the right]
Back to Excel by clicking the Excel icon on left of the toolbar
Test

=Increase(A1,B1)



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
aheitner > said:
Is there a built-in function that computes % gain?

Say, A1=100 B1=150, and I want C1 to compute ((B1-A1)/A1).
For years, I've been just entering the formula, but it seems
that there should be a function that does it for me. If not,
how can I create a custom function to do it?
Click to expand...

There is a function to do this, but you have to follow Excel's @#$&*?! sign
convention.

=RATE(1,0,-A1,B1)

returns 50%.
 
Great! Thanks!

So, if I can indulge you once more: how do I get the function to b
available on any/all worksheets I open or create? Or do I have t
manually add the module to every worksheet?

-Andrew


Norman said:
Hi Andrew!

Revised for missing line:

Function Increase(BaseNum As Double, NewNum As Double) As Double
Increase = (NewNum - BaseNum) / BaseNum
End Function

Select the above and Copy
In Excel
Right Click a Sheet Tab
Select "View Code"
Select the workbook name in the top left window
Insert > Module
Paste [into the code window on the right]
Back to Excel by clicking the Excel icon on left of the toolbar
Test

=Increase(A1,B1
Click to expand...
 
Hi
you may put this function in your personal.xls workbook or create an
add-in. See
http://www.mvps.org/dmcritchie/excel/getstarted.htm
for more details


--
Regards
Frank Kabel
Frankfurt, Germany

Great! Thanks!

So, if I can indulge you once more: how do I get the function to be
available on any/all worksheets I open or create? Or do I have to
manually add the module to every worksheet?

-Andrew


Norman said:
Hi Andrew!

Revised for missing line:

Function Increase(BaseNum As Double, NewNum As Double) As Double
Increase = (NewNum - BaseNum) / BaseNum
End Function

Select the above and Copy
In Excel
Right Click a Sheet Tab
Select "View Code"
Select the workbook name in the top left window
Insert > Module
Paste [into the code window on the right]
Back to Excel by clicking the Excel icon on left of the toolbar
Test

=Increase(A1,B1)
Click to expand...
Click to expand...
 
Back
Top