So our company gets Material Statements which basically state what chemicals and how much of said chemicals go into producing X item.
I need to figure out the percentage of MASS for each chemical.
So data might look like this:
Total Weight of product: 500 grams or mg, etc.
Sub-Item - Contact - 450 g
Material - Copper - 425 g
Substance - Nickel - .05 g
Substance - Copper - 422 g
Substance - Lead - .02 g
Material - PVC Insul - 5 g
Substance - Polysomethings - .5 g
Substance - Red Dye - .4 g
Substance - Plastic - 4.1 g
So basically the sub-item line is 450/500
first material line is 425/450
substance is .05/425
the substance lines use vlookup to find the material line to divide by. formula i'm using is below:
B2 is either sub-item/material/substance
Col I is the mass of that line
M2 is the total weight of the item
Formula works until you get to the Polysomething lines in the above example data. instead of taking the material weight from PVC Insul, it takes it from Copper.
I have a 2-column employee file. Column A has the employee id and column B has the manager's employee ID. The reporting layers can get pretty deep. In this example, there are 6 layers of reporting. For example, Employee id 1200 (which is not in column A) has 2 employees. One of them (1712) has several employees, one of whom (1680) has employees reporting to them and so on for several levels.
Is there a way in Excel (2003 or 2007) to summarize the reporting structure? For instance, I need to know all of the employees that report up through emp id 1712 - not just that report directly to 1712, but all those under 1712's tree.
I have the following function. the problem with this excel is i give it "C:" as directory, it search only one level of sub folders i.e it will search the filename in
C: est C: est3
It does not go deeper. i.e it wont look in folder that is for example
C: estanotherlevel C: est3anotherlevelmylevel3
How to modify my function so that it goes to the deepest folder. is there a way to do it?
Function GetFile(directory As String, filename As String) Dim FSO As Object Dim fldr As Object Dim subfldr As Object Dim file As Object Dim fullname As String
I'm looking for a formula to pull the correct piece of data from one worksheet and display it in another. I have tried VLOOKUP, but it is only returning values from the first instance of my lookup value. Below I have included a sample of what the data from the first sheet looks like. My lookup value is entered on Sheet2 in cell A1. The formula I have used is as follows...
=VLOOKUP(A1,Sheet1!A2:D13,4,FALSE)
The result this would return for a value of 111111111 is 15, but I'm looking for one more level of depth in this lookup, by being able to pull not necessarily the cost of the first code, but of a specific code......
I need to set up an excel sheet with dependent data validation that is multiple levels deep.
ie. Select value from dropdown list in Col A returns dependant list in Col B, select value from the list in Col B returns list in Col C etc.
Is this possible?
Also, is it possible to set multiple values to return the same list? ie in the drop down you have 4 items. Each returns a dependent list but items 1 and 3 both return the same dependent list. Just saves me making many copies of the same list with different names which isn't a huge issue.
I am looking for some assistance to make pivot tables with multiple data values against a control item. A sample excel file is attached herein with. I am quite new to pivot tables was unable to find out a solution self.
Below is the partial copied text of a Multi Level BOM. I am trying to hide or unhide the rows with 4, 5, 6, 7, 8 and greater number of dots. The dots indicate the level of BOM.
I'm sure this will be an easy calculation for most of you...........
If I have two numbers, ie. 390 and 217, how can I calculate the difference between the two figures as a percentage? (These two figures represent sales in two months and I need to know the difference in percentage terms).
I work at a library and have been asked for help by the Asst. Director. The problem is, I have next to no Excel experience, just what I've been reading in the Help files and Online.
We are using Excel 2003 and running XP. Here is his predicament: he has an excel document that lists the books we have ordered from a publisher. Column I has the MSRP for the book and Column J has the discounted price for the book. We are trying to get Column L to show the percentage of the discount that we received. This is what i've come up with:
example for line 255 typed in as formula for L: =1-7.95/14.98
It comes out correct when I type in the formula as I show above, but when I try typing in the formula (as it should look:=1-J255/I255) , i get an error message: #VALUE! ...
I am having a problem formatting a chart in Excel 2010. My chart has multi-level category axis labels, and I would like to have a vertical grid line separating each major group of categories. In Excel 2003, I could right-click on one of the gridlines and then specify the spacing I wanted between gridlines. In Excel 2010, as soon as I indicate that I want multi-level category axis labels, I get a vertical gridline between each category and I am unable to alter the spacing. If I deselect the multi-level axis label option, I can adjust the spacing between the vertical gridlines, but the axis multi-level label functionality is lost. Is there a way to fix this problem without having to resort to using the drawing tools or text boxes to achieve the desired results.
I have XYZ Coordinates for a continuous 3-D line that has numerous segments. I want to input a distance along that line, and have it create the XYZ coordinates at that point. See Image for reference.
Row 2 is my start point - I input the initial coordinates here - this point is the origin of the 3-D line Column B is where I want the calculated Y value to go for each point Column C is where I want the calculated X value to go Column D is where I want the calculated Z Value to go Column E is the how far along the 3-D line that the (to be calculated) point should be at. (MD1) Columns G, H, & I are given to me, and I use this data to generate the coordinates in Columns J, K, & L Column O is the cumulative length of the line at that coordinate. (MD2)
Basically, I had planned on writing a formula to:
Find the coordinates of the point who's MD2 (column O) is before the desired point's MD1 (column E)Find the coordinates of the point who's MD2 (column O) is after the desired point's MD1 (column E)Subtract MD's (column O) to get the length of the segmentFind the distance along that segment that MD1 (column E) fallsUse that distance to traverse along that line to the desired point.
I have a listbox with 8 columns. Multiselect is enabled, and it must stay this way. As part of my program, after the user presses a command button, I need to use the row indexes of the selected rows in order to copy the selected information into an array which is then placed in a different listbox, and then delete the items from the original list. Pseudocode of what I want to do:
[Code] .....
But my understanding is that .ListIndex does not work this way with multiselect listboxes. I've tried searching for a solution for a while, but I cannot find one.
I would like a way to use conditional formatting to highlight percentages that are over 50%. The problem is that I have other numbers in the same column that are not percentages but I would like to ignore them for highlighting purposes? Can this be done without sorting the columns?
eg.
345- ignore Ken- ignore 2500- ignore 45.6%- do not highlight 65- ignore 92%- highlight
The simple formula above calculates percentage which basically in this example, 159-90 divded 159% which equals 56.60%. What i need the formula to recognize is if both the entries are 159 which should show 100%. Its to show error rates by the way. Percentage wise.
I am trying to combine to cells containing percentages, however when I do the percentages turn into decimals. I have tried using the text funtion, but that just returns the full number 1, no decimals or percentages.
I have a table of data with names across the top and number of weeks down the left. The table lists amounts of money those names earned during the respective weeks. It looks something like this:
Mike Dan Bill Carl 400 500 600 700 800 900 1000 800 700 600 500 400
In a seperate worksheet within the same workbook, I have 3 columns of data. The first column is a name. The second column is a criteria. In the third column, I am trying to look up the value of 8 specified weeks of earnings of the name I put in column 1 and multiply those earnings by a percentage based on the criteria in column 2. Then sum the products.
I have a table of the 8 percentages I am trying to apply. There are 7 different percentage applications.
So if I am looking up Mike's earnings during those 8 specified weeks and the criteria in column 2 is "B", then I want to apply the appropriate column of percentages to those 8 looked up earnings, Multply them and then Add them.
Up until now, I have been trying to use some hybrid of HLOOKUP, SUMPRODUCT, and IF.
I have a column of data with students marks ranging from zero to 78(S17:S74). I need to work out the mean score of the bottom 20% of all the scores achieved - Is this possible. I'm no expert so please keep it as simple as possible! I'm working with Windows XP and Excel 2002.
I have data that I import from another source into excel and it pulls over like this
3866.00% 435.00% 125.00% 1254.00%
I want to remove the % since these aren't actually percentages, the report we pull from has them listed incorrectly and cannot be changed since it is software driven, not excel driven. The problem is simply remove the % changes the number to 38.66 instead of leaving it at 3866.
I have a row of formulas that calculates information into a percentage. As you input info, a percentage is created in the cell. Those cells that do not yet have information entered still show " DIV/0" or something like that.
I need a formula that will average all the percentages, but the problem is, it's trying to also average the formulas with the percentages, so I currently get an error.
