$ in functions

  • Thread starter Thread starter bgraczyk
  • Start date Start date
Hi

changes the formula from using relative addressing to absolute :)

try this
in a new workbook set up the following

A B C
1 10 5 =A1*B1
2 5

fill down C1 to C2
notice that you get 0 in C2
now change C1 to
=A1*B$1
and fill down
notice how the value from B1 is used in the calculation in C2

hope this helps
Cheers
JulieD
 
If you are in your Excel worksheet, go to: Help / Contents and Inde
and lookup "Absolute cell references."

This will give you a thorough explanation on what the "$" sign does.
 
Add dollar($) signs to make it Absolute Reference.

=$A$1 + B1

$A1.......absolute column, relative row
A$1.......relative column, absolute row
$A$1......absolute row and column
A1........relative row and column

You can select the cell reference(A1) in the formula bar and hit F4 to cycle
through the combinations.

See Help on cell reference to learn more.

Gord Dibben Excel MVP
 
Just another:

It creates "absolute addressing", as several has stated. But there is no
difference at all between =A1 and =$A$1. It's a valuable help when you set
up the spreadsheet; copy the formulas, fill the formulas down, to the right,
whatever with the formulas. But once done then it does nothing at all.

HTH. Best wishes Harald
 
If you mean in a function name i.e. CHR$ , the $ for string,
% for integer This is old GW Basic, MS Basic coding
you don't need to use those suffixes in VBA.
I think Basic was created at Brown University.
 
Hi David,

According to the help file.

"Some functions have two versions: one that returns a Variant data type
and one that returns a String data type. The Variant versions are more
convenient because variants handle conversions between different types
of data automatically. They also allow Null to be propagated through an
expression. The String versions are more efficient because they use less
memory.

Consider using the String version when:
• Your program is very large and uses many variables.
• You write data directly to random-access files.

The following functions return values in a String variable when you
append a dollar sign ($) to the function name. These functions have the
same usage and syntax as their Variant equivalents without the dollar sign."

Chr$ ChrB$ Command$
CurDir$ Date$ Dir$
Error$ Format$ Hex$
Input$ InputB$ LCase$
Left$ LeftB$ LTrim$
Mid$ MidB$ Oct$
Right$ RightB$ RTrim$
Space$ Str$ String$
Time$ Trim$ UCase$

Although I admit I can never remember to use them in my code <g>.

Cheers
Andy
 
Hi Andy,
Thanks, but since we all use Option Explicit
it shouldn't make any difference to any of us, just the others
who don't <grin>
 
Back
Top