I have a some detail in the tabular form with different criteria as size, colour ,purity,cut ,by combining all these i have price list in a tabular form , if I want to intersect all this and find the price how can i do so
View 9 RepliesI have created the code below to create a Pivot Table using VBA, so that it populates from a list box option.
I would prefer if it didn't show any subtotals and was in a tabular form. It keeps dropping off at that point for each row field.
CODE
Sub trail()
'
' trail Macro
'
'
Dim wksPivot As Worksheet
Dim wksData As Worksheet
Dim pc As PivotCache
Dim PT As PivotTable
Set wksPivot = Sheets("PIVOT")
[Code] ......
I need to add different amounts to a column of prices based on a column of zone numbers. I don't know if that makes sense but here is an example test.xlsx
View 3 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?
I have a list with prices of US Treasury bonds. They are quoted in a partcular way that I would like to convert to decimal form. They are quoted like this eg: 99-02. Now this means that the price is 99 and some decimal. The two first numbers are the number of 32:s. Thus in this case the price is 99 and 2/32. However if there are three decimals eg 99-021 the last digit refers to the number of 124:s. Thus the price is 99 and 2/32 and 1/256.
Sometimes There are only two decimals followed by a plus or minus sign eg 99-30+ or 99-10-. The plus/minus indicates that you should add/subtract 1/64. (This is the correct way, I know it seems strange but that is the way it is). Also number of digits before the line (in the example 99 might be one, two or three, eg it can look like this: 5-01, 99-021 or 100-21). Is there any way that I can convert these price quotes into decimal form using worksheet functions?
On an order form the customer will be able to enter the width and projection of several products that they want to order. The way these products are priced are in a Matrix of Width over projection. The current system i have designed is:
1. The pricing Matrix's have all been put into one big table and given a unique id per product matrix.
2. An advanced filter has then been run and and it extracts the appropriate matrix and copies it onto the process sheet.
3. An Index/Match formula is used to find the price for the inputted width and projection. It does this by finding the intersect point of the width and projection on the table.
Currently this will be put into a macro and assigned to a button.
The problem is that up to 15 (or possibly more) products need to be able to be ordered in one order form. With the current system it means there will be a lot of Advanced Filters and there will be a macro button that will need to be clicked after every product order (and they're could be 15 or more). Obviously this isn't very professional, it is time consuming and must be the hard way of doing it.
I was wondering what over systems that could be used for this sort of thing. The more solutions there are to this problem the better. Attached is the file. If you do post a solution it would be preferred if you could also post a file with a working example in. I find it much easier to learn the solution if i can see it working.
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 am trying to find the median for a huge amount of data, so I have cut it down to just an example size. I have uploaded a copy of the example file.
I have three price ranges that I have to find the median for:
Under $5MM
$5 MM - $25 MM
$26 MM - $56 MM
I need to find the median for each price range for Signed units and Sold units. I also have to find a median within a date range (e.g. within the last six months) that includes the previous criteria.
For the first formula, I have tried using the following formulas:
=MEDIAN(IF($I$14:$I$34,"Signed",IF($F$14:$F$34,">="&0,IF($F$14:$F$34,"<="&5000000,$F$14:$F$34))))
=MEDIAN(IF($I$14:$I$34,"Signed",IF($F$14:$F$34,">=0",IF($F$14:$F$34,"<=5000000",$F$14:$F$34))))
(I remembered to do "CTL + SHIFT and ENTER" to make it an array.)
For the second formula, with the date range, I am able to find the total for the last six months, but not with a price range included.
The formula that I am using that works for the median of units Signed within the last six months is:
=MEDIAN(IF($H$14:$H$34="Signed",IF($G$14:$G$34>"07/31/2013"+0,$E$14:$E$34)))
However, once I try to find the price range, it does not work:
=MEDIAN(IF($H$14:$H$34="Signed",IF($G$14:$G$34>"07/31/2013"+0,$E$14:$EIF$34,IF($E$14:$E$34,">=0",IF($E$14:$E$34,"<=5000000",$E$14:$E$34)))))
i have a some detail in the tabular form with different criteria as size, colour ,purity,cut ,by combining all these i have price list in a tabular form , if i want to intersect all this and find the price how can i do so
View 9 Replies View RelatedI have a table/ array of numeric data. it covers say, cells b3 through to x20. The table/array just contains numeric values from 3 to 500. What I would like to do is to allow the user to click on any cell in that table/array and at the bottom of the spreadsheet in cell d22, to display the data from a cell in row 2 that has been clicked. This data I could the use in conjunction with other lookup tables on seperate worksheets to give the user further information, without cluttering the main sheet
View 2 Replies View RelatedI have actual date , then I have names of customer and I have product reference. And what I want to find out is the latest price COLUMN D for which this specific product was sold to specific customer (Data in rows E,F,G,H are from another tab). So basically I want is to find out that latest price of product x27 sold to customer A on 15.7.2013 was 70 because on 12.7.2013 we have sold it to customer A for 70.
A
B
C
D
E
F
G
H
[code]....
I have a price list Width/Drop
100013001600190022002500280031003400370040004300460049005200
850
1150
1450
1750
2050
2350
2650
2950
3250
3550
3850
I need to index given :-
My ranges are named Width and drop and List. Rules are anything above largest drop/width must return 0. anything below lowest width/drop will be the lowest listed
anything inbetween will choose the value >= to the value. lookup is returning the closest, and my other formula fell foul of nesting, i am working in 2007 but it is targeted at XP/2002.
I get a price list from my distributor, my scale I use for my store is anything below $10 (from my distributor price) gets 30% markup, then anything above $10 gets 20% markup. How would I be able to function this into the list I receive in excel so I can export the prices directly to my store of what the prices should be in my store. Also it would be a plus if at the end I can get it to end in 99 cents. Thank you look forward to your response! I am using Excel 2003
View 12 Replies View RelatedI have a price list with part numbers in Column E and prices in Column C. I want to update the prices from a master list that has part numbers in Column D and prices in Column H and then make only the updated prices bold.
Currently the master list is in a different workbook, but if I need to, I can copy and past the master sheet into the same workbook as the sheet I want to update.
I have around 150 separate price list files and i would like to create a macro to findformat currency then copy 1.05 and use Edit | Paste Special (value, multiply)...... then round up or down to nearest cents .567 would roundup to .57
The findformat and Paste Special will work when i manually do it.... but when i record as macro it will not work when played back...... Here is the code it records... code does not include the roundup part. I dont know how to do that.
Range("M3").Select
ActiveCell.FormulaR1C1 = "1.05"
Range("M3").Select
Selection.Copy
Application.FindFormat.NumberFormat = "$#,##0.00"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Range("M3").Select
Application.CutCopyMode = False
Selection.ClearContents
End Sub
display a sheet in the following format using excel vba ....
View 9 Replies View RelatedHow to find the latest price by each sku in the list, the list contains many skus.
View 13 Replies View Relatedformula getting an ascending order from a long list of numbers.
I use excel for mac, 14.3.9
A B C D
1 123 1.99 123 2.09
2 124 3.99 124 4.09
New prices arrived as shown in column C (code) & D (price)
I want my old price list updated A (code) and B (old price)
Can VLookup use the new data and replace B with information from column D.
I have a price list with type & price, I want do a drop down list for type (this is solve), so when i select list at type, the cell I want it show price.
View 2 Replies View RelatedHi, I've got a price list with 2500 different lines which the boss has decided need a picture against each one (the guys a legend!!). Can anyone help me with a macro that will look up the code in Column A, then add .jpg on the end, and insert a picture into Column J?
View 11 Replies View RelatedI am looking for VBA Macros to compare 2 price list. I am trying to achieve the following.
1. Script look in sheet one i.e. Old list for duplicate if it is their it should display in 3rd sheet i.e.duplicate with sheet name and row number
2. Script look in sheet two i.e. New list for duplicate if it is their it should display in 3rd sheet i.e.duplicate with sheet name and row number
3. Script look in both the sheet for duplicate values it it is there then it should display in 3rd sheet i.e. duplicate
Also it should count the number of records on each sheet in column F1. If possible can we display the work % while checking both the lists.
Sample file is attached.
I have been sent a price list from a supplier and need to create an order form that can be populated from the price lists. I have to pages of price list as well. I will also be adding to these as I start getting other supplied product and need my client to be able to generate a order from these in the simplest way possible. Again I hardly use excel and these docs are all sent in excel format.
View 4 Replies View RelatedI am trying to create an order form. i have a price list from my local hardware store that i want linked to my order form. i want my order form to autocomplete items off the price list. i have tried a few things but im stumped.
View 9 Replies View RelatedI need to complete my business price list. Basically it's an XL sheet with all my suppliers products listed, there price, my margins, and my retail price and bulk price. Currently I'm fiddling with my margins and the best way to implement a formula to reflect these margins. Rather than do it manually, is there anyway I can create a forumla for different gross margins to be set for a certain range of the suppliers goods. For example, any product I buy thats costs me between $0-$20 has a set margin of 1.44 (44%), or a product who's price ranges from $100-$200 has a set margin of 1.26 (26%) etc. This would cut out then need for me to manually check suppliers prices and change to margin accordingly, thus saving me much time.
View 7 Replies View RelatedI've built a spreadsheet that accurately displays my company's price list. However, from time to time, there are increases and/decreases, by percentages. I would like to know how I can build a formula that would allow me to quickly update the pricesheets by the appropriate percentage, without having to manually do so, one cell at a time.
View 2 Replies View RelatedI 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 RelatedHowever I only put in a small price list when i tried extending it the workbook produced error as per attached. I thought i would just need to amend the row numbers but it doesnt seem to work
unable to upload at present but prevoius is available on this thread. http://www.excelforum.com/excel-work...hoice-sum.html. Need to extend price list to cover up to 10,000 lines
The sheet has a price list (I attached the sheet). its a width x height(drop) format. If width or height <= minimum width/height then use the minimum listed. if width or height > minimum <=maximum then lookup in table next heightest value. here is the complication. any oversized items are priced as roundup((size -biggest size) / (biggest - second biggest size),0) * ( price of biggest-price of second biggest). so if my widths are
4600 4700 4800
10 25 35
and I am pricing 5050 I would do :-
calculate howmuch its oversize
5050-4800 = 250
Calculate the difference in the last 2 sizes
4800-4700 = 100
Calculate the rounded up multiples
250/100=2.5 rounded up = 3...........