DOLLER Excel Formula

Author Zaheer    Category Formulas     Tags ,

In Microsoft Excel application, this dollar sign indicates a certain currency format. However, aside from that, its dollar sign contains other additional use for Excel, that is, it indicates to the cell references within formulas. This article will discuss the uses of both dollar sign for Microsoft Excel application.

Dollar Signs Which Indicate Currency Format

If you opt for the numbers to be displayed as financial values, what you should do is to format those numerals as currency. Then to configure the numbers/cells as currency, you can employ either the Accounting or Currency number format. You can find the numerical formatting options on its Home tab, inside its Number group.

Here are the two major differences of the two numerals or number formats:

  • If you apply its Currency format, the zeros will be displayed as zeros (0) and represents negative numbers through employing the minus sign. On the other hand, if you apply the specified Accounting format, the zeros will be displayed as negative numbers and dashes enclosed in a parenthesis.
  • The currency sign will be displayed at the border section of its cell, despite of the span or length of any given number especially if you apply its Accounting format. However, if you utilize the currency layout, its currency symbol will be displayed next to the figure or number.

Dollar Signs which Indicate Cell References

Most probably, you are aware that a known formula pertains to its cells. This is one of the reasons why formulas in Microsoft Excel are very powerful and the obtained results can be altered on the basis of alterations made in some other cells. A formula utilizes a certain cell reference when it pertains to a single cell. This cell references are consists of 3 kinds within an “A1” reference method which is the default: mixed, relative and absolute.

Fixed Cell References

At the time when an absolute cell reference is contained in a formula, then it’s not important which cell is being occupied by the formula, there is no alterations that will take place in its cell reference. Just like what it did to its original setting, the formula still pertains to a similar cell even if you like to copy or transfer it. With regards to its absolute reference, every component of reference (refers to figures or number within the column and the letter pertaining to its row) is always preceded by dollar sign ($). As an example, $B$2 is the absolute reference intended for cell B2. Even if you intend to copy or transfer the formula anywhere, it constantly pertains to cell B2.

Relative Cell References

On the contrary to absolute reference, if you transfer or copy the formula to other cells, the relative cell reference is altered. Moreover, any dollar sign doesn’t come first with every column and row portion of the relative cell reference. As an example, B2 is known to be its relative reference for cell B2. The reference alters based on the columns and rows where it’s shifted or copied. Therefore, if a certain formula containing relative reference B2 is moved from a single cell on its left and a single cell down, its reference changes into A3.

Merged Cell References

Some mixed reference utilizes a “$” sign that appears before a letter that pertains to a row, or the numeral that pertains to its column. As an example, B$2 is considered as mixed reference wherein only the row changes, while the column don’t. Therefore, if a formula comprising that reference is moved a lone cell to its left and a single cell down, then its reference changes into A$2.

Which Cell Reference Must You Use?

Most probably, the type of required cell reference that you employ totally depends on the manner that you are using. However, a relative cell reference is usually utilized. Microsoft Excel utilizes relative cell references via default, thereby, it is easy to change the reference within the formula whenever you copy and transfer the formula across and down. Oftentimes, you may prefer for relative reference since it changes automatically.

But there are some cases wherein you prefer to utilize its absolute reference, that is, when you are using a VLOOKUP function. Although the value you’re searching for may change (like for example, if you transfer a certain VLOOKUP formula a column down), the lookup table’s actual location will not adjust or change even if you tend to move or transfer a formula down the column.

Since you already have an idea about the differences for the three varieties of any cell references, it’s up to you to decide on what to use.

Post comment