Solved

Calculate column of sales and expenses

Posted on 2014-02-01
4
433 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 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
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‚Ķ

756 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