Solved

Calculate column of sales and expenses

Posted on 2014-02-01
4
425 Views
Last Modified: 2014-02-01
Hi experts!  Attached is a spreadsheet that I need some expert assistance with as I can't figure out an easier way to calculate two columns.

For the horizontal bar at the bottom, the net total for sales should be the (qty x sale price) for each order.  The same for the "cost" column.  I started a formula that looked like this:

                  Sale Price
Net Total   =(((d3*c3)+(d4*c4))-((e3*c3)+(sum(f3:i3)+sum(f4:i4))

Since some of the spreadsheets have 50-100 orders, the net total for "sales" and "costs" would be incredibly time sensitive to go back and fix previous months (Oct-Feb).  Is there any other way to calculate this besides one manual entry at a time?  I hope so.  Thanks experts!!
EE-Question.xlsx
0
Comment
Question by:samiam41
  • 2
  • 2
4 Comments
 
LVL 18

Accepted Solution

by:
Steven Harris earned 500 total points
ID: 39827132
As a quick workaround, add a helper column.

In Column K, use:

=SUM(C3*D3) and then copy down to all rows.

Then use:

Net Sale Price - =SUM(K3:K4)
Net (Profit?) in E15 - =(D14-E14)+SUM(F14:I14)
0
 
LVL 9

Author Comment

by:samiam41
ID: 39827150
ThinkSpaceSolutions, thank you for the quick solution.  I plugged it in and it I like the results.  I hide the columns from the view.  When I print, will the hidden columns stay hidden?  I can't remember if that is a setting or not (don't print hidden column).
0
 
LVL 9

Author Closing Comment

by:samiam41
ID: 39827154
Great suggestion and will be the solution I implement.  Thanks ThinkSpaceSolutions!
0
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39827160
When I print, will the hidden columns stay hidden?
Sure will.

Great suggestion and will be the solution I implement.
Glad it worked out!  Helper columns are sometimes the easiest to understand, implement and edit, so it has more value than a script that just does everything for you.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Outlook Free & Paid Tools
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question