Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Calculate column of sales and expenses

Posted on 2014-02-01
4
Medium Priority
?
456 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 18

Accepted Solution

by:
Steven Harris earned 2000 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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

715 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