?
Solved

Need help working calculations in Excel Spread sheet!!

Posted on 2011-09-14
7
Medium Priority
?
336 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 1000 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 1000 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

CTAs encourage people to do something specific to show interest in your company, product or service. Keep reading to learn why CTAs should always be thought of as extremely important, albeit small, sections of websites.
New style of hardware planning for Microsoft Exchange server.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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…
Suggested Courses

770 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