Solved

Need help working calculations in Excel Spread sheet!!

Posted on 2011-09-14
7
329 Views
Last Modified: 2012-08-13
Hello, what I am trying to do is figure out if there is a easy way to automate calculations in a excel spread sheet. Basically I download a sheet from a website, I can’t control what the download contains or the order or anything like that, I just have to take it like it is. So what I need to do is take the numbers that are in each row, for each item, let’s say each item has 4 rows, 1 for sale amount of the item and 3 for different fee’s, what I need to do is calculate the price after the fee’s have been subtracted. I use Excel 2007 and 2003, either one will work if that matters.

So instead of 4 rows that say 100, 10, 5 and 5 I need one row that says 80. I can filter and delete the extra rows if need be but I just need to narrow it down to the amount after fee’s have been taken out. Once I get to that point I have a SQL query that will import it into a Access database and update the inventory records.

Below is a example and I also attached a spread sheet, 4 items with 4 rows each, ideally it would be great if there was a way to automate calculating each item and then creating or editing an existing row with the amount after fees are taken out.

Please let me know if there is a way to make this work. I have never written a macro or gotten really in depth with the functionality of Excel so any help is appreciated.

Thanks,


Date      Inventory Number      Amount      Details
9/14/2011      E1      100      Sales amount of item
9/14/2011      E1      10      Fee 1
9/14/2011      E1      5      Fee 2
9/14/2011      E1      5      Fee 3
9/15/2011      E2      200      Sales amount of item
9/15/2011      E2      20      Fee 1
9/15/2011      E2      5      Fee 2
9/15/2011      E2      5      Fee 3
9/16/2011      E3      300      Sales amount of item
9/16/2011      E3      30      Fee 1
9/16/2011      E3      5      Fee 2
9/16/2011      E3      5      Fee 3
9/17/2011      E4      400      Sales amount of item
9/17/2011      E4      40      Fee 1
9/17/2011      E4      5      Fee 2
9/17/2011      E4      5      Fee 3

Example-Sheet1.xls
0
Comment
Question by:REIUSA
[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
  • 2
  • +1
7 Comments
 
LVL 40

Expert Comment

by:als315
ID: 36538072
If you will import data into Access why not to do this work there? It will be very simple query. Sales amount - Sum(Fee)
0
 
LVL 40

Accepted Solution

by:
als315 earned 250 total points
ID: 36538114
This is query for Access (link your excel file as table Sheet1):
SELECT Sheet1.Date, Sheet1.[Inventory Number], Sum(IIf([Sheet1]![Details] Like "Sales*",[Sheet1]![Amount],-[Sheet1]![Amount])) AS [Amount-Fee]
FROM Sheet1
GROUP BY Sheet1.Date, Sheet1.[Inventory Number];

Open in new window

0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 250 total points
ID: 36538151
If it's always 4 rows with the value of the last 3 deducted from the first then you can do this in Excel. In E2 put this formula

=C2-SUM(C3:C5)

Now leave E3:E5 blank but select all 4 cells E2:E5. Now put the cursor on bottom right of E5 until you see a black "+", now double-click and that pattern is copied all the way down your data

regards, barry
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Expert Comment

by:barry houdini
ID: 36538162
After following those instruction the sheet will look like the attached - it's as easy to do for 1000 rows as for 20...

barry
27308246.xls
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36541724
Barry,

For variable rows, how about SUMPRODUCT with Inventory ID, Sales* & Fee* as the variables.

Thanks
Rob H
0
 

Author Comment

by:REIUSA
ID: 36561302
I'm curious if that would work too?
0
 

Author Closing Comment

by:REIUSA
ID: 36923910
Thanks for the help everyone. I'm starting this project soon.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

FAQ pages provide a simple way for you to supply and for customers to find answers to the most common questions about your company. Here are six reasons why your company website should have a FAQ page
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.

726 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