Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Need help working calculations in Excel Spread sheet!!

Posted on 2011-09-14
7
Medium Priority
?
347 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 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 34

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

Technology Partners: 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!

Question has a verified solution.

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

Over time, the online landscape has altered considerably, but that’s nothing compared to the up-and-coming trends that will shape the web design industry in the coming year. Keep reading to find out which trends will shape B2B web design in 2018.
How do you create a user-centered user experience on your website? And what are some things you should consider in the process?
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
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…

579 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