SOLVED

Formula in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1478221%22%20slang%3D%22en-US%22%3EFormula%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1478221%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%20Can%20someone%20help%20me%20with%20a%20formula%20in%20excel.%26nbsp%3B%20I%20am%20trying%20to%20do%20a%20profit%20and%20loss%20account.%26nbsp%3B%20The%20issue%20that%20I%20have%20is%20that%20I%20have%20a%20product%20that%20i%20sell%20at%20a%20fixed%20cost%20price%20and%20I%20want%20to%20be%20able%20to%20automatically%20insert%20a%20formular%20that%20says%20if%20i%20have%20sold%204%20of%20these%20items%20the%20expense%20will%20be%20X.%26nbsp%3B%20so%20I%20have%20created%20columns%20so%20that%20I%20just%20have%20to%20enter%20the%20number%20of%20items%20sold%20and%20I%20want%20the%20next%20column%20to%20calculate%20the%20expense.%26nbsp%3B%20I%20can%20do%20it%20for%20one%20row%2C%20but%20if%20I%20drag%20the%20formula%20down%20to%20other%20rows%2C%20it%20automatically%20changes%20the%20cell%20for%20the%20fixed%20cost%20which%20is%20then%20not%20correct.%26nbsp%3B%20as%20an%20example%20if%20I%20have%20my%20fixed%20cost%20price%20in%20C1%20and%20in%20B4%20I%20have%20a%20cell%20that%20I%20place%20a%20quantity%20in%20I%20then%20do%20a%20formula%20in%20C4%20which%20says%20%3DSUM(B4*C1)%20which%20gives%20me%20my%20total%20expenses%20depending%20on%20number%20in%20B4.%26nbsp%3B%20However%20if%20I%20drag%20that%20formula%20down%20to%20other%20rows%20it%20changes%20the%20formula%20to%20%3DSUM(B5*C2)%20adding%20a%20row%20to%20each%20formula.%26nbsp%3B%20I%20want%20column%20B%20to%20change%20depending%20on%20my%20quantity%20but%20for%20the%20figure%20in%20C1%20to%20always%20remain%20constant.%26nbsp%3B%20Is%20there%20a%20way%20to%20do%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1478221%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1478237%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1478237%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F705256%22%20target%3D%22_blank%22%3E%40juliedenman%3C%2FA%3E%26nbsp%3B%20Hi%20there.%26nbsp%3B%20One%20of%20the%20most%20important%20things%20you%20can%20learn%20as%20you%20start%20to%20use%20Excel%20formulas%20is%20the%20use%20of%20the%20%24%20symbol.%26nbsp%3B%20This%20%24%20symbol%20can%20be%20used%20in%20a%20cell%20reference%20in%20front%20for%20the%20column%20or%20the%20row%20or%20both%20and%20will%20LOCK%20it%20so%20it%20will%20not%20change%20during%20a%20fill%20or%20copy%20operation.%26nbsp%3B%20So%20in%20your%20example%20the%20formula%20should%20be%20written%20as%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUM(B4*%24C%241)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ETechnically%20since%20you%20are%20only%20filling%20or%20copying%20down%20in%20the%20same%20column%20you%20could%20use%20%22C%241%22%20since%20the%20relative%20location%20of%20the%20column%20won't%20change%20and%20the%20%241%20will%20lock%20it%20at%20row%201.%26nbsp%3B%20But%20for%20a%20fixed%20cell%20location%20like%20that%20I%20recommend%20using%20the%20%24C%241%20so%20if%20later%20you%20decide%20to%20make%20a%20copy%20of%20that%20cell%2Fformula%20to%20another%20column%20to%20modify%20the%20calculation%2C%20it%20will%20still%20point%20to%20that%20correct%20cell.%3C%2FP%3E%3CP%3EBest%20of%20Luck.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1478251%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1478251%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3BThank%20you%20so%20much%20that%20worked%20perfectly.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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

@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

@mtarler Thank you so much that worked perfectly.