Solved

Need help working calculations in Excel Spread sheet!!

Posted on 2011-09-14
7
299 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
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 39

Expert Comment

by:als315
Comment Utility
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 39

Accepted Solution

by:
als315 earned 250 total points
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
Barry,

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

Thanks
Rob H
0
 

Author Comment

by:REIUSA
Comment Utility
I'm curious if that would work too?
0
 

Author Closing Comment

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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Envision that you are chipping away at another e-business site with a team of pundit developers and designers. Everything seems, by all accounts, to be going easily.
Read about why website design really matters in today's demanding market.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now