What does $A$# do

[Lball]

Is this a viable entry in a formula? I was given a formula with it in it and told to format cell type as #;#;""

 

[Barrie Davidson]

The use of "$" in a formula is called Absolute Cell Reference and is a viable entry in a formula. An absolute cell reference will not change when you copy it whereas a Relative Cell Reference will change when copied. To understand this:

• In cell B1, put this formula: =$A$1
• In cell B2, put this formula: =A2
• Copy the range B1:B2 to H1:H2. You will not that the formula in H1 reads =$A$1, it hasn't changed the cell it's referencing. The formula in H2 reads =G2, the cell it's referencing has changed.

Does this help you out?

 

[Lball]

I know about relative reference, but what is the #(number sign) for?

 

[Norie]

As far as I can see this is nothing to do with formulas.

That's a custom number format which you would apply via Format>Cells... and Custom.

 

[Barrie Davidson]

That's just formatting the cell. The use of # will display only significant digits and does not display insignificant zeros. In addition, the last part ("") will display nothing if the formula returns a value of zero. The semi-colon is used to differentiate how to display different values and is broken as follows:

Positive values (define how to display postive values);Negative values (define how to display negative values);Zero (define how to display a zero value);Text (define how to display text)

For a better explanation, search Excel's help for "Create a Custom Number Format".

Hope this helps.

 

[Lball]

thanks,

So $A$#is not going to work in a formula, correct?

 

[Barrie Davidson]

Correct.

 

[Lball]

Thank you!