Jun 19 2020 08:51 PM
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?
Jun 19 2020 09:42 PM
@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.