I want to match the quantities of buy and sell appearing the same line but in different columns based on FIFO. In case of mismatch, the excess quantity should be transferred to a new line below the same column in which there is a mismatch.
View 2 RepliesI am not good at excel. I would like to split buy and sell orders from the live feed and updating when new feeds come in.
LIVE FEED
BUY/SELLQTYPRICE BUY QTY PRICESELL QTY PRICE
B 4150
S 6200
S 4300 CHANGE TO
B 3350 B 4 150
B 2180 B 3 350 S 6 200
S 2220 B 2 180 S 4 300
B 1120 B 1 120 S 2 220
So i have many different list of items that i have supplied, pretty much shopping lists. I am now trying to create an inventory of my warehouse and i would like to see what i should buy more of and less of. is there a certain table or template or something i can plug in all my lists and have excel break down all the data and tell me whats what on all my info. i have about 45 different lists and each list has an average of about 150 line items.
View 1 Replies View RelatedI am looking for a formula which will calculate a sell price with 20% profit after taxes and costs. I need it to factor in a fixed shipping cost (eg £10), a 7.5% Amazon fee of the sell price, then 20% tax of the sell price. I currently have a formula which essentially does this but as I'm not good with Excel it requires me to input all the values every time which is not practical considering our large inventory.
View 3 Replies View RelatedSalesman
Sales Amount
Introduced by
sales
introduced
sales
indirectly introduced
sales
Tom
300
none
Tom
300
[Code] ........
I have salespeople who I want to pay a bonus to but I've also offered them a bonus on how much the salesmen they referred to the company sell and this goes like a pyramid down 3 levels.
so I have a list of salesmen next to there name is how much they have sold then who introduced them. from this I need to make a list of each salesman and below all the people he introduced and the people they introduced with the sales in the cell next to them.
To show how it could look I've done an example above of the information I need for tom, as you can see all the data is in the first 3 columns and tom needs to show that he introduced bob & Sue, but because Bob & Sue also introduced someone, tom needs to show he was involved in this as well and jo, terry, mark need to show, preferably in a different column as the bonus for them is lower.
I have multiple invoices for ordered products style number and quantity, these invoices are keep adding as orders are coming. I need a separate work book where all items summed. shows style and summed quantity from all invoices.
for example wb1
style quantity
or12 5
or34 2
or45 4
[Code]...
wb3 same style numbers but maybe not in the same column or row, wbs are keep adding as orders are placed.
I need a separate wb
shows style and pluses all quantities
style quantity
or12 6
or34 5
or45 6
I have built a pivot table that shows parts used during a period of years. I need creating a formula that would sum each part by a particular year.
The table is as follows
A part number
B date
C total quantity
I want to display the totals on a separate sheet.
formula on the attached sheet. I need to update it now. The strengths are going to stay the same, but the weaknesses (purple headers on spreadsheet) only have to show up if the value associated with it is 3 or greater. (So traits with quantities of 1 and 2 will not show up as a weakness)
I have attached a spreadsheet wit desired results.
example attached: If data repeats in column A, then add those corresponding values in column B. Can this be done without VBA?
View 5 Replies View RelatedI have 3 worksheets: Income; Expense; Consolidate.
In the first two sheets i am entering, by dates, quantities that are getting in and out of the warehouse.
My code copies that information in the consolidated sheet.
What I need is to make a code that Calculates the "Daily Quantities" and "Rent", based on quantity in the warehouse, that I am paying each day.
I am trying to get a formula for material quantities
What I want to do is
If length is less than 3mts I require 2
If length is more than 3 meters than I require 3 and 1 more for every 3mts after that
e.g:
2mts = 2
3mts = 2
4mts = 3
6mts = 3
7mts = 4
9.1mts = 5 and so on
I have been trying to work with to get my stock table classified into 3 different stock statuses and it comes out, it does not work I have attached the table as it is, the formulae I wanted to use and the end result I would like to have.
View 7 Replies View RelatedBelow I have attached a sheet for keeping track of the number of cables a person produces per day using Drop-Down list. I would like to have a totals area automatically calculate the corresponding cells. I started the Totals area in column AF.
View 13 Replies View RelatedI need to sum a column when the quantities meet two criteria. The name in one column, and the date in another column. Probably an easy formula, but new at this and having trouble.
View 4 Replies View RelatedI have a sheet which is just a list of product codes and a sum of every order placed for each code. i.e.
Sheet2 Â AB1Prod codeOrdered2123100345650478960
then I have another sheet which is the orders placed by our customers. These orders are to go out in 6 periods each of 2 weeks.
For various reasons the amount ordered each time won't match the periods.
The sheet beforehand will look like
Sheet1 Â ABCDEFGHIJKLM1Prod codeP1 REQALLOCP2 REQALLOCP3 REQALLOCP4 REQALLOCP5 REQALLOCP6 REQALLOC212327Â 20Â 35Â 20Â 12Â 11Â 345633Â 40Â 50Â 15Â 25Â 11Â 478915Â 20Â 20Â 20Â 20Â 10Â
What I need to do is look up the quantity ordered and then allocate the ordered quantity to the periods carrying over the remainder to the next period and so with the table above the result would be
Sheet1 Â ABCDEFGHIJKLM1Prod codeP1 REQALLOCP2 REQALLOCP3 REQALLOCP4 REQALLOCP5 REQALLOCP6 REQALLOC2123272720203535201812Â 11Â 34563333401750Â 15Â 25Â 11Â 478915152020202020520Â 10Â
Both sheets are sorted by the product code ascending and it doesn't matter if it is a formula or VBA based solution as I am already using both.
The number of product codes is currently just over 400 but will grow to about 550 by the end of period 6.
I need to sum up the batch quantities for a date with variance one...
but it doesn't work... I suspect that I'm using wrong formula, it should be not SUMPRODUCT...
when I tried to use just SUM, it adds all the quantities in the colomn.
=SUMPRODUCT(--(($AB$11:$AB$100)=AK12),--($AG$11:$AG$100=1),($AD$11:$AD$100))
I'm having some problems to find a way to distribute quantities from a known average price. Here's the Example:
My Client bought 100.000 Shares from a Stock, in various prices.
Qtty Price
10041,71
30041,61
70041,78
1.50041,72
4.40041,68
5.50041,75
10.00041,94
13.50041,70
64.00041,80
Then the client wants to distribute this trades for 7 different funds at the same average price.
Qtty Fund 1: 294
Qtty Fund 2: 541
Qtty Fund 3: 1.213
Qtty Fund 4: 13.370
Qtty Fund 5: 16.582
Qtty Fund 6: 28.002
Qtty Fund 7: 39.998
I'm sending an example attached.
I have a Bill of Materials (BOM) that has been flattened from a database into an Excel format. There are many BOM levels and I need to multiply the quantity of a component by the quantity of the "parent" it reports to. I have attached a sample file with raw data and the expected result (calculated manually). My goal is to create a formula that calculates the "Ext. Qty" column in my expected result. EXT_QTY.xls
Here are some of the rules for the raw data structure:
1. First BOM level at very beginning is always "1"
2. All children of BOM level "1" have a BOM level of "2"
3. All children of BOM level "2" have a BOM level of "3"...
4. There are potentially many BOM levels (approx. 15 levels for full set of data)
5. All of the BOM level "2" items are children of the most recent BOM level "1" assembly and there will be multiple level "1" items, level "2" items, etc.
6. Data is always sequential so the children (and grandchildren) will always come after the parent
I have three values. The Quantity of items, the cost per items and the cost for the entire order. In some instances I want to give my clients items for free so I need to lower the cost per item in order to keep the entire order cost the same.
In order to ensure we are on the same page let's use the following associations:
Y = Quantity
X = Cost Per Item
Z = Order Total
So essentially I need a formula that will automatically calculate the cost per item (X) when more Quantity (Y) is added in order to keep the order total (Z) the same.
I'm using excel 2010.
I'm trying to filter a data set to see only those groups that have a subtotal count of 3 or greater. The subtotals are at each change in customer to count the number of divisions that customer is associated with.
In other words, how can I see a list of only those customers who have the permission to shop 3 or more brands?
Excel Help.xlsx
I have a list of items and quantities I need of each, such as
cat 3
dog 2
mouse 1
horse 4
snake 2
leopard 4
I'm thinking that it would be a macro that would be way to go for this project.
I want to run a macro that will make the list above the following
cat 1
cat 1
cat 1
dog 1
dog 1
mouse 1
horse 1
horse 1
horse 1
horse 1
snake 1
snake 1
leopard 1
leopard 1
leopard 1
leopard 1
So what happened was that 6 lines of information was transformed into 16 lines of information. My purpose is that I will then will exploring various combination of these items, and thus I think that splitting them up like this will make them more manageable to work with. Since the original list values will change I will not always know how many lines to set aside for the individual breakdown.
I want to allocate the Quantities which is available in DC based on Sales, Store OH and In Multiples of Pallet Rounding (PR).
For Eg. If I have 200 Qtys in DC, Excel should allocate these 200 Qty's in multiples of PR to highest selling stores and also consider the store On Hand.
Below is the Format
Article #
Store #
Region
PR
Model
[Code]...
I've created a Excel spreadsheet. In the Spreadsheet I've got one worksheet which has columns and list of codes.
I would like my other worksheet to look through find any products I've ordered and list them with their codes and quantities, creating a summary of just what I want so that I can e-mail just the important bits.
I am trying to accomplish is to associate each product on the Distribution tab in 'E' to its associated quantity in 'F' so I can count the total number of each product and display this in 'D' on the Totals tab. Once this is done, the total for each product would me multiplied by the associated price in 'C' on the Totals tab to achieve the total item value for each item in 'E'. I have included the current results and what the correct values should be. As you can see, I tried to use 'Countif' with no success. I could not find any solutions in the forum that were close enough to this situation to work.
View 4 Replies View RelatedI have a table with client ID in column A (range A2:A200) and qty ordered by those clients listed in column B (range B2:B200). Clients’ ID numbers range from 101 to 999.
Except through filtering, how to return the sum of quantities ordered for stores with ID number between 100 and 199 ONLY?
I am trying to put all my parts with quantities on a seperate sheet called "Parts List" Every time you select a quanity for one of the parts, I want it to pop up on my parts list. This will make it easier to identify the exact parts I want and also the quantity I need. This will be much more convenient then scrolling down my parts list and trying to find the one's with quantities.
I think I need to use a vlookup or even a Macro but I don't know how to go about doing this.
Say I have a worksheet that has a column in which every cell is formated in hh:mm:ss format. The column is probably 100 cells long...so from A2 to A101 (A1 is the title of the column). Now, we need to take the data in cells A2:A101 and graph it, but the data needs to be formatted in only seconds. So, in column B, we want to convert the data from column A into seconds format.
For example,
cell A2: 00:01:46
cell B2: 106
Of course, we can't convert each cell by hand, because this needs to be done on a lot of data...worksheets upon worksheets.
It is a product list of office stationary with codes, descriptions and quantities. Using this spreadsheet we input into the quantity how many we want of the item, all well and simple you could call the list a stationary order form.
What I would like to be able to do with this Giant list is to have a function that would extract items off the list into a cleaner consolidated form. As in, if I wanted to order some of item A, C and F, I would like for the second table to just Show ACF without every letter in between or blank cells.
I know a simple method would be to have an IF formula to return the information to a new sheet that I could simply remove blank cell rows (unordered items) and ready for print each time. But I would love to know if there is a better way this could be achieved.
I have basic/intermediate knowledge of excel and can setup the data if need be in order for the function to work, but I can't seem to find the correct function for this project.
to formulate Excel formulas to obtain the average buy price and average sell price for me to do this futures trading. Thanks a lot. I downloaded the Htmlmaker to post the spreadsheet here to show the manual way to calcualte the average buy price and average sell price but when it is on html form, i clicked on the 'Please click this button to send the source into clipboard' button & then i paste into this thread. Is the way to make my spreadsheet appear here correct cause it cannot work.
View 9 Replies View RelatedI am having some trouble getting a formula to work. I am building a report that pulls figures from a pivot table in another workbook. I am using a vlookup with match function to get the column index to find the relevant data I want. Where I need to add two columns together I am using sum, with the vlookup & match formulas nested in them e.g.:
=SUM(VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("FAID",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("BPCM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMD",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE))
Where:
F13 = Employee number
Column C on the pivot 156 workbook is where the employee number is based.
The Match formula is then getting the column index from the column headings of the pivot table ie. "FAID"
This in itself works fine, as long as it finds a match in the column headings. This is where i get the error as in the above function "COMD" is not in the pivot table. However I need to keep it included as it may appear on a future pivot table. Is there a way of getting the sum function to complete even though later in the formula it can't complete the vlookup? So it will ignore it, or assume the value is zero if it can't find it? The formula probably needs to do this for all the vlookups as some headings may drop off in future pivot tables.