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 two worksheets, one is setup with a part number only and the months Jan-Dec. The other worksheet has those same part numbers, but it also has the quantity and the months Jan-Dec. I need to do a lookup to find the part number, multiply it by the quantity on that given line, and then multiply it by the quantity in the month. I will show you how my worksheets are setup:
Worksheet2: P/N Jan 123456 Sum of ALL Jan Demand * the Quantity of each item 345678 Sum of ALL Jan Demand * the Quantity of each item 456789 Sum of ALL Jan Demand * the Quantity of each item
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 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.
The first tab has the following values in one table: ordinal of car manufacturer, car manufacturer, number of cars sold in 12 months (seperated in different columns)
The second table has: car manufacturer ordinal, car manufacturer and the price of one unit.
Second tab has the following: one table with the following columns: ordinal of manufacturer, car manufacturer and all 12 month's names seperated, this table is the same as the first table in the first tab.
Now, when i type for ex. ordinal 1 in this tab it should give me the car manufacturer, and it should multiply the value of number of cars sold in all months, for ex: if there are 5 chevrolets sold in january, then multiply 5 x n (where n is the price of one unit) and print that value in column january.
I have a workbook with several different worksheets which I am copying from the individual sheets and pasting into a summary sheet, and multiplying the numeric numbers by minus 1 to reverse the signs.
This is all working fine, but I now want to copy an additional worksheet called "Plan" and paste this into the same summary worksheet, however I do not want to reverse the signs for this data.
All of the other worksheets begin with "Total" in the worksheet name, and this is how I am specifying which sheets to copy. I can re-name the "Plan" sheet to "Total Plan" if that makes the logic easier, the import thing is that the signs are not reverse for this sheet.
Column Z of the summary worksheet "RDBMergeSheet" identifies which sheet the data was originally copied from, so could I somehow use this to say if column Z = Plan, skip the multiplication by minus 1?
My current code is this:
VB:
Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _
I'm trying to calculate Interest accrued from a specific date. The problem is payments can be interest only which when used should keep the next months interest at the same rate instead of decreasing because the principal hasn't been paid down.
Im having problems when over the course of a number of payments they are principal + Interest and Interest only which requires at times to add up the same interest amount for 2-3 months in a row, then add up a different interest rate, etc. Additionally if the reference date is say on the 15th and the interest date is the 1st then for the last month I need to calculate the interest on a daily basis based on the current interest rate for that month.
The reference cell is H5, the Date ranges are J2 - J12, and the principal fields are: K2 - K12.
In the next post I will include the Amortization schedule.
In this example the interest is accrued at the same rate months 1-7, then decreased for months 8 and 9. so the total interest accrued should be eqaual to: $7487.74 (see next post for schedule).
Microsoft Excel - ZZ_Loan.Calculator-Update.1.1.xls___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutH6H7H8H9H10H12H13H14=FGHIJKLM1 v 1.0 PaymentApply DatePrincipal + InterestAdditional PrincipalInterest Only2 1/1/2009 833.333 2/1/2009 833.334Quarter Close Backward 3/1/2009 833.335 Close Date9/1/2009 4/1/2009 833.336 Days of Interest0 5/1/2009 833.337 Beg Principal Balance$100,000.00 6/1/2009 833.338 Paydowns$980.41 7/1/20091,321.51 9 End Principal Balance$99,019.59 8/1/20091,321.51 10 Net Principle Due$3,562.50 9/1/2009 829.2711 Interest Due & Accrued 10/1/20091,321.51 12 Interest Earned (cummulative)$7,350.66 11/1/20091,321.51 13 Interest pymts (cummulative)$7,350.66 14 Net Interest Receivable$0.00 TEST [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I'm absolutely new to Excel and I've been trying to workout a formula to to test two conditions if true and derive the data from a 3rd column. In my attached excel file there's a Grade (A) , Status (B) and Mid Point (C) Tables. I'd like it to be formulated in a sense that forexample:
IF Grade= 1 AND Status= Q4 RETURN =3750*0.25 IF Grade= 1 AND Status= Q3 RETURN =3750*0.5 IF Grade= 1 AND Status= Q3 RETURN =3750*0.75 IF Grade= 1 AND Status= whatever (I mean whatever I input in this cell then it would retrun 3750 ofcourse satisfying the condition grade= 1) RETURN =3750
IF Grade= 2 AND Status= Q4 RETURN =5500*0.25 IF Grade= 2 AND Status= Q3 RETURN =5500*0.5 IF Grade= 2 AND Status= Q2 RETURN =5500*0.75 IF Grade= 2 AND Status= whatever (I mean whatever I input in this cell then it would retrun 5500 ofcourse satisfying the condition grade= 2) RETURN =5500 ................
So, another question with regards to this sales sheet that i am setting up. I want to be able to take the information below and for each of the people listed (i.e. by their initials) i would like to...
1.) Take their associated % (Columns C, E, G) 2.) Multiply the % by the Revenue (Column H) 3.) Sum up the total for each of the names (Locatedin Columns B, D, F) 4.) Only sum up the ones that occur in Month 1 of the Year 2012
I have tried multiple ways with a Sumproduct but none of them have worked. I would love to be able to do this with a single formula without having to add another column, for instance, to track what MonthNum it is. I tried using some kind of offset, but i could never get it to work.. For instance to look through the range, find the initial go over 1 column to the right and multiply that % by the revenue if the Month is 1..
Date (A)Rec1Name (B)Rec1Pct (C)Rec2Name (D)Rec2Pct (E)Rec3Name (F)Rec3Pct (G)Revenue (H)1/1/12AD25.00%RU25.00%BF25.00%1000.001/1/12AD25.00%RU25.00%PNF25.00%2000.001/1/12BF50.00%00.00%00.00%1200.002/1/12BF50.00%00.00%00.00%1000.002/1/12BF50.00%00.00%00.00%1000.003/1/12AD25.00%RU25.00%00.00%1000.003/1/12AD25.00%RU25.00%00.00%1500.004/1/12AD16.67%RU16.67%BF16.67%1500.004/1/12BF50.00%00.00%00.00%1500.00
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.
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.
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.
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.
Below 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.
I 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.
I 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.
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
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.
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?
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'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.
I 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?
