Function for rate of increase re two values

R

rleonard

Is their an excel function to give rate of increase or decrease between two numbers

the formula i use is (New Amt - Orginal Amt) / Orginal Amt = Increas

Thank
Bob Leonar
[email protected]
 
T

Tushar Mehta

No. What you are doing is the way to go. However, if you want for
whatever reasont to use a function, write a user defined function. Put
the following in a standard module.

Option Explicit

Function FractionChange(OldVal As Double, NewVal As Double)
FractionChange = (NewVal - OldVal) / OldVal
End Function

The function is used in a worksheet cell as =FractionChange(C11,D11)
where C11 contains the old value and D11 the new.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
R

rleonard

Tushar

I put code in a module Personal.xls as Option Explicit! But when I open another worksheet i can not find function FractionChange! How can i fix this so function is available to all my worksheets

Thank
Bob Leonar
[email protected]
 
C

Chip Pearson

Bob,

You need to prefix the function name with the workbook name.
E.g.,

=personal.xls!FractionChange(1,2)

It is something of a misconception that functions in personal.xls
are available to all workbooks, as though there were something
special about the workbook. Personal.xls is just a regular
workbook, but one that is generally always open. You still need
to tell Excel to look there, though.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


rleonard said:
Tushar,

I put code in a module Personal.xls as Option Explicit! But
Click to expand...
when I open another worksheet i can not find function
FractionChange! How can i fix this so function is available to
all my worksheets!
 
T

Tom Ogilvy

Also make sure it is in a general module and not a sheet module or other
special module like workbook or userform or a class module. You get a
general module when you go the vbe and with personal.xls as the active
project, do Insert => Module.

when you declare the function make it public

Public Function FractionChange(rng1 as Range, rng2 as Range)

End Function


--
Regards,
Tom Ogilvy

rleonard said:
Tushar,

I put code in a module Personal.xls as Option Explicit! But when I open
Click to expand...
another worksheet i can not find function FractionChange! How can i fix this
so function is available to all my worksheets!
 
Top