matrixx Posted January 5, 2015 Posted January 5, 2015 Hi, I have a question guys, Is there a way for me to use a formula where I can put in the Quantity of a product e.g. Coffee and excel can calculate the total automatically in a different cell? Do I need a separate sheet for a price list of all the items I have? I would really appreciate some help from someone I have uploaded the spreadsheet for your perusal ThanksWEEKLYSALESBREAKDOWN.xls
Seb1780 Posted January 5, 2015 Posted January 5, 2015 You will need to put the unit prices of each item in somewhere, but this need not be on a different sheet - just add another column after the item name to hold it. Each of the daily cells can then hold the number of sales of each item; in your "daily total" box you then construct the formula along the lines =(number sales item1 * unit price item1)+(number sales item2 * unit price item2)+....... The weekly totals work in a similar way. You could put the unit price into your formula, but having them listed somewhere makes changing your prices easier!
matrixx Posted January 5, 2015 Author Posted January 5, 2015 Yeah I did think I may need to do that. I will give it a go and post if I have executed it successfully! Thankyou very much for the very promptly reply, greatly appreciated.
matrixx Posted January 6, 2015 Author Posted January 6, 2015 I feel embarrassed posting this, but I can understand the formula in a sense, but I have no idea exactly how to input it! It's giving me an error. Is anyone able to give me the formula based on the cells I have created? I have uploaded the file again. Really sorry to be a pain.WEEKLYSALESBREAKDOWN.xls
Seb1780 Posted January 6, 2015 Posted January 6, 2015 In D33 type =D12*C12 and this will give you the total value of soup sold on Monday. If you change this formula to be =(D12*C12)+(D13*C13) you will get the total value of soup and sandwiches sold om Monday. Continue adding to the formula so you end up with all the items and a formula that looks like :- =(D12*C12)+(D13*C13)+............+(D32*C32) You will need to add the bits to replace +.............+, you'll learn by doing it! Once you've done one column the rest should be easier to understand.
tmcd35 Posted January 7, 2015 Posted January 7, 2015 (edited) Although it can be pretty easily set up with what you've got now, as @Seb1780 above, I think you're trying to do too much with one table. Personally, I'd do this... 3 sheets... Prices, Sales, Totals On the Prices sheet have a simple table listing items and their price. On the Sales sheet have a table of items for each day, just filling in the number sold On the Totals sheet have the same table as you have now, each cell just multiplies the daily sales in the relevant Sales table with the releavent Price in the Price table... (Cell Total!D12) = Sales!D12 * Price!C12 You can then autosum the columns and rows to give daily totals and weekly totals. Edited January 7, 2015 by tmcd35 1
matrixx Posted February 13, 2015 Author Posted February 13, 2015 Thankyou for everyone who helped me! Very much appreciated!
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now