Need help working calculations in Excel Spread sheet!!

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
REIUSAAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
als315Connect With a Mentor Commented:
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
 
als315Commented:
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
 
barry houdiniConnect With a Mentor Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
barry houdiniCommented:
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
 
Rob HensonFinance AnalystCommented:
Barry,

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

Thanks
Rob H
0
 
REIUSAAuthor Commented:
I'm curious if that would work too?
0
 
REIUSAAuthor Commented:
Thanks for the help everyone. I'm starting this project soon.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.