Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
inventory formulas
I want to use a spreadsheet to track inventory and value of inventory.
I think a 4 column spreadsheet would do it the way I need to track items. The first column would be Items. Tomato sauce might be item one for instance. The second column would be Quantity. Under Quantity on the Tomato row might be 12 cans. The third column would be Case or Tub Price and would provide the units per case and case price (so it might read 5 per case $25). I need a formula for the fourth column which would be Price per item or pound (i.e. $5/can) which is derived by dividing the number of items per case into the total cost of case. The last and fifth column would also need a formula and would be the Value of Inventory. So if there were only 4 cans, the value for that row (the tomato sauce) would be 4 x $5 = $20. I'm sorry to cover this in such detail but I have been doing this all by hand every day. How do I put in the formulas to automatically get the correct amounts without having to do the math every day?? Thanks very much to the kind soul who helps. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
inventory formulas
You won't be able to combine the Case Price and Units Per Case. Excel will
treat this as text and your formulas won't work. If we have 5 columns Column A - Item Description Column B - Quantity Column C - Case Price Column D - Units Per Case Column E - Inventory Value A2 = Tomatoes B2 = 4 C2 = 25 D2 = 5 In cell E2 type =C2/D2*B2 then select cell E2, click on the black cross in the bottom right corner of the cell and drag your mouse down as far as you need to go. Be sure not to combine text with your numbers as Excel will treat the entire thing as text instead of numeric data. Of course, if Column D is 0, you will get a divide by 0 error in Column E. To clean this up you can change the formula to =IF(D2=0,"",C2/D2*B2) "marsha" wrote: I want to use a spreadsheet to track inventory and value of inventory. I think a 4 column spreadsheet would do it the way I need to track items. The first column would be Items. Tomato sauce might be item one for instance. The second column would be Quantity. Under Quantity on the Tomato row might be 12 cans. The third column would be Case or Tub Price and would provide the units per case and case price (so it might read 5 per case $25). I need a formula for the fourth column which would be Price per item or pound (i.e. $5/can) which is derived by dividing the number of items per case into the total cost of case. The last and fifth column would also need a formula and would be the Value of Inventory. So if there were only 4 cans, the value for that row (the tomato sauce) would be 4 x $5 = $20. I'm sorry to cover this in such detail but I have been doing this all by hand every day. How do I put in the formulas to automatically get the correct amounts without having to do the math every day?? Thanks very much to the kind soul who helps. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
inventory formulas
Wow, thanks!!!!!!!
"JMB" wrote in message ... You won't be able to combine the Case Price and Units Per Case. Excel will treat this as text and your formulas won't work. If we have 5 columns Column A - Item Description Column B - Quantity Column C - Case Price Column D - Units Per Case Column E - Inventory Value A2 = Tomatoes B2 = 4 C2 = 25 D2 = 5 In cell E2 type =C2/D2*B2 then select cell E2, click on the black cross in the bottom right corner of the cell and drag your mouse down as far as you need to go. Be sure not to combine text with your numbers as Excel will treat the entire thing as text instead of numeric data. Of course, if Column D is 0, you will get a divide by 0 error in Column E. To clean this up you can change the formula to =IF(D2=0,"",C2/D2*B2) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif formulas change after doing a sort | Excel Worksheet Functions | |||
Array Formulas take waaaay too long... | Excel Worksheet Functions | |||
Need Formulas for counting multiple conditions | Excel Worksheet Functions | |||
Problem with named formula's | Excel Worksheet Functions | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |