Making Average Buy Price And Average Sell Price
Aug 13, 2008
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
ADVERTISEMENT
Feb 14, 2014
Trying to calculate the lost revenue value for when a piece of machinary is offline for a period of time based on the hrly price during that particular period. The machines come on/off line at various times during the year so i am trying to make this a bit dynamic so i only need to add new info and not have to change the formula
Column F - Off date of machine
Column G - Restart date of machine
Column H - Hrly time
Column I - Hrly price
I thought this formula (an array) would work for me
=average(if((Column H>=Column F) * (Column H<= Column G), Column I)) but it only calculates that average price for the first period and not new average prices for other periods.
what adjustments I can make to my formula or is there a better formula to use?
View 1 Replies
View Related
Jul 19, 2012
I have the following data, where a negative number represents a sell of shares and a positive number a purchase and column C shows at what price the transaction happened.
A
B
C
D
E
F
1
Ticker
Quantity
Price
Ticker
Average Price
[Code] .......
And I've been asked to calculate the average price per ticker. For now it doesn't matter whether it's based on a first in first out principle or first in last out principle.
View 9 Replies
View Related
Dec 10, 2006
I'm having some problems to find a way to distribute quantities from a known average price. Here's the Example:
My Client bought 100.000 Shares from a Stock, in various prices.
Qtty Price
10041,71
30041,61
70041,78
1.50041,72
4.40041,68
5.50041,75
10.00041,94
13.50041,70
64.00041,80
Then the client wants to distribute this trades for 7 different funds at the same average price.
Qtty Fund 1: 294
Qtty Fund 2: 541
Qtty Fund 3: 1.213
Qtty Fund 4: 13.370
Qtty Fund 5: 16.582
Qtty Fund 6: 28.002
Qtty Fund 7: 39.998
I'm sending an example attached.
View 4 Replies
View Related
Aug 1, 2012
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.
View 6 Replies
View Related
Nov 23, 2013
I am trying to get and average price for a total quantity of jacks
Material Price row G cells 4 - 56
Material Labor Price row I cells 4 - 56
Jack Quantity row L cells 4 - 9
I had this =SUM(G4:56)+SUM(I4:56)/SUM(L4:9) just wont work?
View 5 Replies
View Related
Nov 29, 2012
I am looking for a formula which will calculate a sell price with 20% profit after taxes and costs. I need it to factor in a fixed shipping cost (eg £10), a 7.5% Amazon fee of the sell price, then 20% tax of the sell price. I currently have a formula which essentially does this but as I'm not good with Excel it requires me to input all the values every time which is not practical considering our large inventory.
View 3 Replies
View Related
Sep 5, 2012
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
View 1 Replies
View Related
Jun 9, 2008
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 Related
Jan 4, 2010
The 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?
View 4 Replies
View Related
Jul 24, 2006
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,
View 7 Replies
View Related
Feb 12, 2010
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.
View 12 Replies
View Related
Mar 17, 2009
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
View 3 Replies
View Related
Jul 14, 2009
I have a unit price and a quantity. I want to be able to take the sum of the extended price without having to add a column for extended price. I don't want to just hide it, either.
Example attached.
View 2 Replies
View Related
Jun 14, 2013
E11 through E24 contains numbers and a few errors (#N/A) that need to persist (the errors need to show).
E10 needs to show the average of the numbers that are in E11 through E24, and just ignore the errors.
I have many columns like that - where the errors need to show and I need to show an average of the number/values that do appear, ignoring the errors.
View 14 Replies
View Related
Dec 9, 2008
i have two columns...a and b (a w/ names, and b w/grades). then i have the table lookup with names and grades all mixed up for many rows. i want to be able to average the grades with appropriate names.
=average(vlookup(name, table, column, false))?? i don't get it to work and how can i specify the grade to average?
View 5 Replies
View Related
Apr 7, 2007
I am trying to find out how to set a formula that allows me to price point. Our marketing department want all product prices to be either to the value of 6 or 9
eg:
$72 becomes $76
$77 becomes $79
View 9 Replies
View Related
May 10, 2013
I have a task to rank the stocks from the highest price to the lowest price. I've made a template and managed to get the ranking right. I used the LARGE() function.
But now the difficulty is to get the 'Stock name' related to the ranked price. I may do it with the MATCH() function. But the problem is there is an equal number 15%.
Can you have a look at the example attached here?
View 5 Replies
View Related
Mar 16, 2006
My goal is a price code for cost of goods. In G1 I have "BLACKHORSE". I need a code will convert 1 to B, 2 to L, BLA for 123 and so on. This formula is giving me BBBLBB for 1. I don't need cents, just whole numbers.
View 14 Replies
View Related
Sep 16, 2009
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 Related
Aug 2, 2014
A1 cell includes; Dividend Payment : TWITTER (National Market) %39,06 (Closing Price: 5,28 Dolar), Cash Dividend I want 5,28 result in B1 cell ?
A2 cell includes; Dividend Payment : FACEBOOK %5,00 (Closing Price: 53,18 Dolar), Stock Dividend I want 53,18 result in B2 cell ?
In order to understand my question open enclosed file.
Note1: "Closing Price:" text in the cells are stable.
Note2: "Dolar" text in the cells are stable.
View 7 Replies
View Related
Jan 6, 2014
I'm trying to set a formula that will calculate the price difference between two identical items in two different sheets. The items' order is not the same and each list includes some different items along the identical ones.
View 11 Replies
View Related
Jul 18, 2008
using Excel 2002 on XP.
My partner and I are selling products. He gets 5% of the sales price, then I get the rest. But I want to make at least $2 on every sale. So, let's say the item cost us $50. If he wants 5% off the top, and I want at least $2, how do we calculate what to sell it for?
I tried the following, but it didn't work:
$50 (cost)
+ $2 (my profit)
+ 5% (partner's profit)
------
$54.60 (sales price)
It doesn't work because I end up with $1.87.
$54.60 (sales price)
- 5% (partner's profit)
- 50 (cost)
------
$1.87 (my profit)
I've tried other things, but I always end up under $2. Is it possible to calculate this? or do I need to have a percentage for myself? If Excel can't do it, do you know of any calculators out there than can?
View 9 Replies
View Related
Nov 17, 2008
I'm making a work sheet for items and would like to have the lowest price in a column automatically flagged by bolding, color change, font change or something.
View 14 Replies
View Related
May 8, 2009
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.
View 5 Replies
View Related
Jan 4, 2010
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 Related
May 9, 2014
I have a price table with about 70,000 line items (some lines are duplicates).
There are about 12 columns each with different qualities such as item name, size, thickness, price and etc.
I've been building this table in excel and just importing it into Access for the time being.
When a customer sends me an order, I'd like to be able to look up the items in my price table (matching across multiple columns) and automatically return the price for each item.
So far, I've been using the following formula....
=LOOKUP(2,1/($A$1:$A$60000=D1),$B$1:$B$60000)
With A5:A60000 = price list concatenate so all columns in 1
D1 = item I'm searching for
B1:B60000 = prices for items
The only thing is that this is very labor intensive.
The only thing is my customers will typically submit an order and it is NOT in the same format as I need it to be.
So it's very labor intensive and I spend hours just editing their order in a spreadsheet so that it follows the format I need it to.
View 1 Replies
View Related
Feb 1, 2007
I 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.
View 9 Replies
View Related
Apr 9, 2009
I'm trying Calculate Curved Price Rate by SF. phil.freestyle@gmail.com
View 5 Replies
View Related
Jan 26, 2009
I have a list of items that each one has starting price, demands and current price. Current price changes by demands according to pre determined scale. Demands values are entered manually by user. I’m looking for a formula that will calculate current price automatically according to the scale & demands. I cannot know what will be the amount of demands (it could be 0 or 5000...) but I do know that demands are sequential integer numbers, positive or negative. I also don’t want it to exceed the minimum and maximum value that determine by user. I think the attached file will explain well than what I’m writing.
View 5 Replies
View Related
