I have a list of part numbers that have duplicates because each time there is a different unit price the part number is listed.In my excel file, I also have quantities for each unit price the various different part numbers were sold at. I need a way to
identify: for each part number, what the unit price was for the highest qty sold. I am involved in a project at work and this information is needed.
i am currently working on cost analysis of large projects.
I have a worksheet with about 100 headings and i would like to sum up the totals of 'quantity x unit price' for each product.
I tried the sumproduct function but it did not work and i had to do the '=sum(b4*c4,d4*e4,f4*g4,..........) to get the result in the total column. I attach a small sample of the file.
I can't seem to get my head around this one. I have a list of ranges of prices for different materials depending on the quantity bought. I want a formula that returns the correct price depending on the quantity required.
Please see attached workbook. My desired results are in column S. I can't stop thinking that a nested IF statement is the solution so I was hoping for a fresh pair of eyes on it.
For this ordering tool, I would like to calculate and present the grand total, starting from a fixed range of two columns (each 10 rows), where one has quantities, and the other has the price/unit. Since I'm not allowed to change the layout of the ordering sheet (we still fax orders...), I can not add a column for the subtotals. Also, not all rows are filled necessarily.
I had some success with this as a worksheet function: =IF(ISERROR(H30*I30);0;H30*I30)+..., but I would really like to have it as a part of a big VBA function, since colleages might alter/delete the worksheet formula.
I looked and messed around with For loops, but I can't make it work.
I'm using Excel 2010 on a Windows 7 machine.
I have one sheet with a large list of dates and total prices.
I have a second sheet with a list of dates and unit prices.
I want to divide the total price in my first list by the unit price on my second list.
I want to somehow point Excel to the date in the first list, and then lookup the corresponding date in the second list to use the correct unit price.
My thoughts thus far have been along using VLOOKUP with WEEKNUM and YEAR but I've been unsuccessful.
I have a worksheet that is made up of the following:
Column A : Date
Column B : Time
Column C : Price
I want to use these two functions, to pull the highest price:
I have a a spreadsheet for work that has 7,000 lines.
All are french wines. with 7 or 8 columns like Price, Wine, Vintage , etc.
I have situation where I have identical wines from different sources.
What I need to do is keep the one with the highest price and either hide or delete the others.
For instance. I have 10 lines of a wine 2005 Leoville Las Cases. 5 Lines are in regular sized bottles. The other 4 are 1.5 L MAGNUM Sized bottles . Is theres a function or formula that will pick the 1 line with the highest price from each bottle size (and in my spreadheet of 7000 each wine as there are hundreds) and delete the others or hide or some way of sorting them?I just need to remove duplicates and be left with the Item with highest price. If there ends up with 2 or more with same high price can it randomly leave 1 and delete the others?
So basically of all the columns only the following 4 variables would need to used as variables: VIntage, Wine, Bottle Size and Price.
is there a way to highlight the highest value in a column.
=MAX(A1:A50) will write the highest value in whatever field the formula is written in, but is there a way for the number itself to be highlighted in its given location within the colum.For example, if A 21 contains the highest value in column A, can that cell be highlighted?
I'd like to have a list printed of all the "qualifying people" found in a range, and then sort their corresponding price values from highest to lowest in Columns A and B. EXAMPLE:
RANGE: D3:D20 - Numerical
RANGE: E3:E20 - Text (names)
RANGE: F3:F20 - $$$
I'd like to search column D for any values of 2 or higher. When it finds a 2 or higher, I want it to find the corresponding name in the SAME ROW in column E, and of course the corresponding price in the SAME ROW in column F. Then I would like only those qualifying people "with value of 2 or higher" to be listed in order from highest price to lowest price in Column A, and B.
COLUMNS
D--------E-----------F---
0-----Mike Bob-----$52.65
1-----Dave Jon-----$42.50
2-----Jane Doe-----$37.65
0-----Gary Lon-----$25.50
0-----Joey Saw----$35.65
2-----Mike Jon-----$35.65
1-----Kate Low-----$38.68
2-----John Doe-----$40.00................
I need J22 to multiply based on years in B22 AND increase 5% for each of those years (compounding) after two years (excludes year 1 from 5% increase). In addition the cell needs to remain blank if D22 is blank. B22 = 1, then the stockprice needs to remain the same, and only increase by 5% after year 1.
Currently...
B22 = a number of years indicated by the formula: =IF(A22="","",DATEDIF(A22,I3,"y"))
J22 =IF(ISNA(VLOOKUP(D22,stockprices,2,FALSE)),"",VLOOKUP(D22,stockprices,2,FALSE))
Example:
If J22 stockprice lookup is $1000.00, and the number of years listed in B22 is 6, then the reported value in J22 needs to be $1494.40.
Windows 7 Ultimate / Excel 2010
I have calculated the implied volatility for different single options using the newton raphson method. But, I also need to calculate the implied volatility which minimizes the sum of squared differences between the observed market price and the model price for each day. I guess one needs to use vectors (jacobian matrix) to do this, but I do not know how to expand the code to be able to do this. Anyone have any idea how this can be done? I have attached the [code] I have used to calculate the implied volatility for one option.
View 2 Replies View RelatedThe analysis basically has 2 data components to it:
The 1st part, is a basic transaction list of shopping items bought through the year. Each transaction's shopping item also has the quantity of that item purchased at that time.
The 2nd part, is a pricing sheet for all the different types of shopping items. The pricing sheet has different prices for different quantities at which the item is purchased.
What I am trying to do is to find the relevant price for shopping item, which depends on not only what the item is, but also the quantity. In point form, it should follow the logic below:
1) Identify the item in the shopping list (worksheet 1) from the list of prices (worksheet 2)
2) Find quantity in the prices worksheet that is closest to the quantity in the shopping list (i.e. where the difference between the quantity on transaction list and the quantity on the pricing sheet is the least)
3) Pull the price for this "closest quantity"
I have uploaded a worksheet showing the structure of that data.
[url]
Is there some VB code I need to do this, or can it just be a few simple formulas?
All data is located within one book. I have two sheets with material codes in each sheet which include pricing (existing and current)
Sheet1 (has existing material codes plus existing pricing) Has about 1200 lines
Sheet2 (has current material codes plus current pricing), has about 36000 lines
I need to cross check if the material code (taken from sheet1) are still available in sheet2, and if they are, copy the current price back to sheet1. The current price needs to be pasted back into sheet1 (next to the existing price). If the material code doesn't exist (for whatever reason, in sheet2), the program needs to move onto the next line and leave the current price for that material code blank. The program should finish once all the lines in sheet1 are completed. I have attached a sample of what I'm trying to do,
I have have a large array of prices (across rows) and am looking for the closest price to match a price that I have been provided with. It's a basic benchmarking exercise on a row by row basis....and the price can be positive or negative. Is there a clean way to reference the closest price?
I have come across a fair amount of solutions, but none worked optimally - particularly the =INDEX(Data,MATCH(MIN(ABS(Data-Target)),ABS(Data-Target),0)) approach....it just didn't work for some lines, and only worked for values less than source price in other instances.
I would also like to reference the source on the next column.
I am trying to do an if statement where I ask if the 2009 price is .50 or less away from the 2008 price, bring back "Check" See below:
2008 2009
$23.95 $24.15
Using excel 2007
have the following code
With Charts("Chart1").Axes(xlValue)
.MajorUnit = 1
End With
The default is in months. What is the VBA code to change it to years? to days?
A1= 8:40, B1= 3:05 (15:05)
C1= I need a formula to give me the number of units between A1 and B2 (1 unit equals 15 minutes)
D1 = the remainding minutes
Example Answer to above would be C1= 25, D1 = 10
All data times will be in increments of 5 minutes and usage of military time is acceptable.
I'm attempting to put together a script which in simple terms finds the 'Highest and Second' highest activity and associated FTE for a given sub group.
To be more specific:
On the "Slide 1" sheet look at the values in column J starting at row 7 until blank;For each value, then search the "All Monthly Direct Activities" sheet in column C for the same value, excluding the 'Subtotal' rows;Where a match is found, compare all the values in column E for that sub group;And Find the highest and second highest figure;
Once these are found copy this figure and associated value in column B and paste onto the "slide 1" sheet.
I'm not particularly well versed in writing VB, but I've been working with the code to see if I can get this work, which I have been unable to do.
I have attached a file which may perhaps provide a greater detail than my description of the problem containing the 'Source' and 'Destination' sheets.
If I use =MAX(A1:G1) I get the highest value. Is it possible to show the next highest rather than the highest and if so how?
View 9 Replies View RelatedI'm working on a charting application that uses Office charts. I'm wondering what are some of the main reasons people change the base unit from the default, and how common that is.
Some reasons I've identified on my own:You're comparing different monthly metrics, but they could be recorded on different days of the month.Get a zoomed-out view.Compare the max value of each month.
I want to use two separate Unit of measurement according to two condition in a same cell.
View 3 Replies View RelatedI've been looking on the internet for a spreadsheet that lists the various units of measure with conversion factors to other units. I'm not looking for the formulas, just a chart.
View 3 Replies View RelatedHere's the table for one game
Date-----Buy(Unit)-----Sell(Unit)-----Net Unit-----Price($)-----Net
Profit/Loss($)
xx/xx----2----------------##--------------2--------------125---------- ##
xx/xx----##---------------1---------------1--------------150---------- ##
xx/xx----##---------------1---------------0--------------165---------- +65
xx/xx----##---------------4--------------(4)-------------170--------- ##
xx/xx----2----------------##-------------(2)-------------165---------- ##
xx/xx----##---------------1--------------(3)-------------180---------- ##
xx/xx----3----------------##--------------0--------------180---------- (10)
Number in bracket means negative.
## means empty cell.
How can I type a formula, so that every time there's no stock left (ie zero net
unit), it will calculate the net profit once.
If you take a look at the spreadsheet you'll see that there are 11 sessions. I'd like to figure out a way by looking at the attendance log to get the percentage/number of people from a certain unit that attended each session. The Y's means that they were present and the N's means that they weren't.
I tried = countif( range,"12WT") which I guess is wrong because it only counts the number of times the word 12WT appears. It doesn't count the number of time a person from 12WT attended the session which is what I'm looking for. How do I go about getting the percentage of people that attended from each unit?
For all the units that didn't attend, is there a formula that would list the units that didn't attend according to the session number? For instance for session one, someone for EC, 9WT, didn't attend. Is there a way that the end result for the formula could be: EC,9WT. Or, does the result for every formula always have to be numeric?
I need to calculate a royalty rate due which is based upon a Unit Price * Unit Sales. The royalty rate due changes at certain levels of sales.
I've attached sheet to hopefully make clear.
I'm trying to return only the following portion of the text from a large list.
So, I want to have just the following phrase appear in a column:
Original
AAM UNIT AAM HIGH 50 DIVIDEND 13 $77,666,423 $1,132,491
To
AAM HIGH 50 DIVIDEND
Original
AAM UNIT AAM HIGH 50 DIVIDEND 13 $77,666,423 $1,132,491
To
AAM HIGH 50 DIVIDEND
Original
FIRST TRUST UNIT FT HIGH DIV EQUITY 15 $13,140,862 $301,295
To
FT HIGH DIV EQUITY
So, I essentially, want any words that appear between "Unit" and the first number that appears before the $ sign (in this case 13 and 15 respectively, but the numbers can be anything - "Unit" stays the same down the list).
I have a worksheet that logs equipment activities. Equipment may already have a standard unit # assigned to it or it may need a temporary unit # assigned for tracking. In a separate worksheet is a column of predefined temp unit #'s that can be assigned and next it is a column to place an "X" once a unit # has been assigned.
I need to write logic that, when an "X" is entered in Column F (Assign a Unit #) of the Activities worksheet, the Unit # (Column G) will automatically select the next available Temp Unit # in the Assign worksheet...and mark that Unit # as no longer available. Quite frankly, this may be more easily done with VBA, but I am not experienced in that at all. I have attached a sample worksheet showing the format of the unit # to be assigned, etc.
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 have a spreadsheet representing a month where I am trying to figure out different scenarios for employees. One scenario is that an employee could have to move to a temporary position. In that case, I need to calculate the salary payments to temporary employees in a particular work unit. I've tried several different approaches to this problem, but am still getting the error.
One method has been using this sumproduct formula:
I am looking for a way to get a spreadsheet to automate calculations of unit costs based on variable packaging names.
I have a series of packages that are denoted by text phrases. Examples:
4/6/12
2/12/12
6/4/12
18/12
24/12
For our purposes let's say the package names above will always be in column A. Column B contains the frontline price of a case of product represented by the phrase in column A. Column C will contain the cost per unit of product - this is obtained by dividing column B by the number of each package arrangement that can be found in one case. Most of the time the number of package arrangements per case is denoted by the very first number in the package name (ie, 4/6/12 would be 4). This won't always be the case though (18/12 would be a package arrangement of 1).
I am looking for a way for the spreadsheet to do all of the following and return the results in column C: if the package name contains "4/6/12", divide column B by 4; if the package name contains "2/12/12", divide column B by 2; if the package name contains "6/4/12" divide column B by 6; if the package name contains "18/12", divide column B by 1; if the package name contains "24/12" divide column B by 24.
