excel+training - formulas

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » excel+training - Formula's

excel+training - Formula's

resolvedResolved · Low Priority · Version Standard

Mandy has attended:
Excel Introduction course

Formula's

What does the dollar sign mean.

RE: Formula's

Hi Mandy,

The dollar sign represents an absolute fixed column, row or cell.

If you copy or fill formulas to other cells, Excel usually updates them to match the appropriate Row or Column.

For example, if I enter the formula =sum(A1+A2) in cell A3, and then copy and paste that formula into cell B3, Excel will assume I want the formula changed to =sum(B1+B2).

However, if I enter =sum($A$1+$A$2) and copy/paste to B3, it won't change an element with dollar sign in front, and keep the formula the same.

It's also handy if you have a cell with a very specific value (eg. current exchange rate for GBP to USD). Say you have it in cell A1. At least you know that throughout your document, you can put $A$1 and it will ALWAYS reference that cell, no matter where you move your formula.

When editing a cell, you can press F4 to toggle between the variations of the relative, absolute and mixed styles of referencing.

Excel Help describes the difference between the two as:

- Absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.

- Relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.

Hope this helps.

Regards, Rich

 

Training courses

 

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Generating Random Numbers

To generate a random number in Excel use the = RAND() function.

The value returned will always be between 0 and 1. To convert this to some other random value, you will need to multiply the result by the highest number you want to consider. For example, if you wanted a random number between 1 and 25, you could use the following code line:
= INT(25 * RAND()+ 1)

Since RAND() will always returns a value between 0 and 1 (but never 1 itself), multiplying what it returns by 25 and then using the Integer function INT on that result will return a whole number between 0 and 24.

Finally, 1 is added to this result, so that x will be equal to a number between 1 and 25, inclusive

View all Excel hints and tips


Server loaded in 0.09 secs.