SOLVED

Highlighted
New Contributor

# Formula in Excel

Hi,  Can someone help me with a formula in excel.  I am trying to do a profit and loss account.  The issue that I have is that I have a product that i sell at a fixed cost price and I want to be able to automatically insert a formular that says if i have sold 4 of these items the expense will be X.  so I have created columns so that I just have to enter the number of items sold and I want the next column to calculate the expense.  I can do it for one row, but if I drag the formula down to other rows, it automatically changes the cell for the fixed cost which is then not correct.  as an example if I have my fixed cost price in C1 and in B4 I have a cell that I place a quantity in I then do a formula in C4 which says =SUM(B4*C1) which gives me my total expenses depending on number in B4.  However if I drag that formula down to other rows it changes the formula to =SUM(B5*C2) adding a row to each formula.  I want column B to change depending on my quantity but for the figure in C1 to always remain constant.  Is there a way to do this?

2 Replies
Highlighted

# Re: Formula in Excel

@juliedenman  Hi there.  One of the most important things you can learn as you start to use Excel formulas is the use of the \$ symbol.  This \$ symbol can be used in a cell reference in front for the column or the row or both and will LOCK it so it will not change during a fill or copy operation.  So in your example the formula should be written as:

``=SUM(B4*\$C\$1)``

Technically since you are only filling or copying down in the same column you could use "C\$1" since the relative location of the column won't change and the \$1 will lock it at row 1.  But for a fixed cell location like that I recommend using the \$C\$1 so if later you decide to make a copy of that cell/formula to another column to modify the calculation, it will still point to that correct cell.

Best of Luck.

Highlighted
Best Response confirmed by juliedenman (New Contributor)
Solution

# Re: Formula in Excel

@mtarler Thank you so much that worked perfectly.