I have a security order list. This consist of several columns most important are
Column A Column B
Sec a 15,00
Sec b 14,50
Sec c 16,00
Sec a 15,10
Sec a 15,70
Sec b 16,70
Sec a 14,80
The first column gives the name of the security, the second column gives the bid price.
What I want is that I want to sort out all bid prices for a specific security. Vlookup works fine but again it just gives the first record. How can I get all bid prices for security a on a new worksheet without blank cells. If possible I would like to have it sorted as well.
I have a sheet that will not let me use macros. I can record them, but when I go to use them it says that the Macro security level is too high. I change it to Medium or Low but it doesn't make a difference.
The same spreadsheet works with Macros on another computer, but not my main one.
I have a 1640 line database in Excel to sort in a specific manner and create a booklet in Publisher for a Family History group.So here is the data as it appears at the moment;
SURNAME - FIRST NAME - DATE 1 - F SURNAME - F NAME - PARISH DATE 2 Date 3 SURNAME - FIRST NAME - DATE 1 - F SURNAME - F NAME - PARISH DATE 2 DATE 3 So A to Z sort Column A BUT retain DATE 1-3 in the finished state,and same for next 400 groups.
Is there any way to filter/sort a workbook by a specific text. (EX. Unit 17) I have a spread sheet with 40,000 plus rows and in 1 column it has descriptions. I am needing the filter to filter out all occurrences of Unit 17 and Unit 16. They will not always say the something happened to them. EX Repair brakes on Unit 17 or maybe repair tires on Unit 17...
I have a large excel file with the top seven rows such as you see in the attached file. In row five there is a bunch of info before the month and the year. What I want to do is get rid of that info besides the month and year. I than want to sort each column by the month and year so that all the columns are in sequential order.
I am looking at trying to do a complex rounding within Excel. I have a spreadsheet of all my product with my cost. I then convert to show what margin I want to sell my items at and then round to .99.
What I am looking at doing is getting my pricing more uniform so that instead of having 23.99, it is 24.99 or 20.99 it is 19.99. I want all the numbers in the ones' spot to round depending on a set parameter.
I have played around with if statements, but can not get it to work fully. Basically if the ONES number is 0 to 2 I want it to round down to 9.99 (ie first was 21.99 I want it to be 19.99) from 2.01 to 4.98 to round to 4.99 and then 5.00 to 9.98 round up to 9.99.
I have a spreadsheet with rows of entries that are listed by date. I would like to create anther worksheet where i can refer back to the other sheet and calculate the total prices per work week. Is there a way to do this using a function(s)?
I have to update our ticker(US and Can stock) prices manually in our portfolio system.
I use www.bigcharts.com as our source . I can import the file in Excel. So what I would like to do is update the prices in Excel from the bigcharts.com. Not just today's prices but prices from previous day or two as well. Once I go to the website, I click historical quotes, then type in the stock symbol, and the date ,then write down the price.
Would Excel web query be the best way to approach this? If so, how would I import the the price for that particualr ticker for that date from the website into my excel?
Refer to the attached sample workbook, (Keep in mind that there is an error in the sample workbook; Im not sure how to remove and re-attach the sample workbook as this is my first day on this forum.)
Cell F19 should contain =D19*B19
Column 1- These values will not change and represent price/cost of each UNIT Column 2- "UNITS" the quantity or number of items at column1 value Column 3- "$ AMT" the total cost of UNITS at that price/cost, i.e. Column1*Column2
Im looking for a way populate the values in Column2 when, "NUMBER OF UNITS" AND "AVERAGE COST PER UNIT" are changed
Ideally speaking, As few units in one row as possible to allow for a good mix. For example: What i don't want is $9000*86 for 774k.
If it helps: "AVERAGE COST PER UNIT" will always be a whole number rounded up to the nearest 1000.
I believe a Most Even distribution of units will work fine.
After the distribution/population of fields in column2;
D29 must equal F3 F29 must equal F7
I'm looking for something that will populate the values in D12:D27 while adhering to the two conditions above. Even when the Input at F3 and F4 are changed.
The values in "UNITS" represent the number of a specific cost item in inventory when added together do not exceed the "TOTAL COST OF INVENTORY". Depending on store location, the "AVERAGE COST PER UNIT" and/or the "NUMBER OF UNITS" will change. It will be used to help forecast how many of a specific cost item should be purchased based on the overall average unit cost without exceeding the budget or inventory space.
I have a sheet with a price list of papers and sizes. I then use this list to calculate the price of each order. It's a basic VLOOKUP and it returns the price. (this is the section outlined in blue on the demo).
However, i now want to add on another section so that if the order has Lamination (Gloss or Matt) this automatically adds on 20p or 25p respectively onto the sheet price. (table outlined in red but doesn't yet work).
I have attached a sample spreadsheet for reference.
What I am trying to do is eliminate column 'J' on the Distribution tab. I want to use column 'C' on the Totals tab to calculate the subtotal based on matching the products on the Distribution tab in column 'E' with the Products list on the Totals tab in column 'A'. Once the match is made, I need to multiply the associated price in column 'C' on the Totals tab and the associated quantity in column 'F' on the Distribution tab with the result going in column 'K' on the Distribution tab.
but neither works and I do not know what else to try.
I am having a problem combining two databases. I have database E and F. I need to take any price that is $0.00 from Database E and replace it with the data from database F. The catch is that there isnít always a price to replace it with. Also, there are parts in database E that do not appear at all in database F. Below is an example (there are about 20,000 lines of data total)
In the examples above I have placed both databases together. Column four has the database designation. As you can see the 1748 Hose reducer has a price for F but none for E.
Basically I need some formula like the following:
If (part number xxx) and (part number XXX) from column 1 are the same, replace the price data from E with the price data from F but only if E = $0.00
I need to do a price increase on an Excel spreadsheet. I need to increase existing prices by 10% and round up to next dollar. I am not sure to to accomplish this in one step on an entire column at one time. I can copy and paste the existing prices into another column and increase them by paste special multiply by 1.1 but not sure how to also roundup the prices.
I have enclosed a copy of one of the pages in the price list so you can see what I am attempting to do. As you can see I need to increase the prices in column D. Column E and F auto increase off of D so I do not need to do anything to those columns. ... Example.xlsx
I wrote code that works just fine, however, I realized there were parts of the data I was using that had 0's in it for holiday.
Bottom line, this is code that calculated the weighted average of prices for 12 months (12 rows across) and Drows across. I'm trying to use the "do while is empty" approach but can't get the syntax to work. Here's the code
Code: For r = 1 To drows Totwavg = 0 Tothours = 0 priceavg = 0
Is it possible with 1 formula and avoid to create 300+ individual formulas.
The background info: each day we check the prices of bananas, sometimes we check 2, 3 or 4 times in the same day, and sometimes the price that is checked is the same, but most of the time the price is diferent throughout the day.
We need to know the average dayly price for a particular day, while taking in consideration that some days we have 3 diff prices, and some others we only have 2 or even just 1 price that was checked and thats the price for that day.
we have the following spreadsheet
Column A = Dates Dates when a price was checked, note here that there might be days of the year when in a single day the price is checked 2 or 3 times, and sometimes the price is different on the same day.
Column B = Price The price checked on the date indicated in column A
Column C = Daily Avg Price Date is just a date range from 08/1/11 to 07/31/12, a single day on each cell, that is intended to be the day reference for column D. C1 = 8/1/11, C2 = 8/2/11, C3= 8/3/11 and so on until 7/31/12
Column D = Daily Avg Price In this column we need to show the average daily price calculated from the prices and dates in columns A and B.
For example: when C1 equals 8/1/11, then D1 has to equal the average price for the product, taking in consideration the amount of prices annotated for that day in column A, with the prices of Column B for that day and providing the average price.
I could have had an easy =Average formula but, since sometimes we have 3 or 2 or 4 equal or different prices in one single day in column A, I dont have a constant for the data range for excel to calculate an average.
My only current solution is to go on each cell on column D and writing the average formula selecting the range that would apply for that average price. But that is taking so much time, and i have multiple other products and year ranges and markets that require this same answer.
I have the following Macro which groups same part numbers in an excel sheet and also creating a blank row after each (or set of same) part numbers. Their prices are in the adjacent cells. I want to add the price cells and the total to appear in the blank cell below the prices. I have one part number in Cell say A1, Its price in Cell B1 i have another same part number in A2, its price in Cell B2 (The macro has grouped them together) Now I want to add cell B1 & B2 and the result in B3 (Row 3 is blank, created by a macro after each group of similar part numbers)
This process is to be repeted in the entire worksheet.
Sub InsertRow_At_Change() 'part number Dim LastRow As Long Dim X As Long LastRow = Cells(Rows.Count, 1).End(xlUp).Row Application.ScreenUpdating = False
For X = LastRow To 3 Step -1 If Cells(X, 1).Value Cells(X - 1, 1).Value Then If Cells(X, 1).Value "" Then If Cells(X - 1, 1).Value "" Then Cells(X, 1).EntireRow.Insert Shift:=xlDown End If End If End If Next X Application.ScreenUpdating = True End Sub
I wanted to do a sheet for calculating prices for my work. I want to have 2 cells where in one I would type in manufacturer price and in other one I would get my price after markup. I have a list of price ranges that I don't know how to make a formula for. For example:
If price is $0-$74.99 than add $50 If price is $75-124.99 than add $75 If price is $125-174.99 than add $100 and so on til $7000
with outputting a table with calculations based on the quantity that the user enters, and depending on that quantity, will calculate the price based on if he/she will receive the discount.
I think I should use an IF statement and calculate depending on if they receive the discount or not, but I don't know how to do that for one type of product code and not have to go manually put it in one by one.
I've attached a speadsheet to show how I should output the data. The prices will be listed in column E, with the prices depending on the quantity that the user will input with an input box and then if he/she qualifies for a discount.