Formula in Excel

[juliedenman]

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]

@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.

[#3 · Accepted solution]

@mtarler Thank you so much that worked perfectly.