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))
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.
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.
I`m relatively inexperienced with Excel, searched for a few days and have not been able to figure out how to calcuate this value. I have multiple worksheets to work out financials. I have a separate worksheet with the pricing breakdown based on volume. 2 columns
I am trying to figure out if something here is even possible. I have an order form and a price list. I get different pricing based on the quantity I purchase. I would like to have the price auto populate based on the quantity entered. The price will be on another sheet.
I couldn't find a lot of info but I also may not know where to look.
if (b2<62) then price is ($sheet2.b2) if (b2<124) then price is ($sheet2.c2) if (b2<372) then price is ($sheet2.d2)
I'm using a spread sheet to determine what I should charge customers. I want to make at least $300 profit on every job. If my profit is not at least $300 then I must up my price until profit reaches $300. Currently I'm manually adding a percentage (Factor) to the original price until I reach $300 profit. In the image below:
A is the original price B is the factor by which I multiply the original price to increase it C is the new figure which is reached after multiplying (1+B2)*A2 D is the costs related to that job (I enter these manually on a separate sheet within the workbook) E if the profit (C-D)
I use a factor in B because when I increase the price, then I have to increase several parts of the job by the same percentage so I multiply each part by this factor to increase the price of each by the same percentage.
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?
I am looking for a formula that can change the price of some of the items on sheet1 in column C by the amount found on Sheet2 in Column D. I would like it to base the calculation on column C in sheet2 (so I can choose if I want to add, subtract, multiply, divide, or make the price exact). I would like all prices that don't match these UPC codes to remain unchanged.
Intended Results can be seen on Sheet1 in Column E. Not sure if I should a formula with the Vlookup function or a macro, or maybe there is even a better solution.
Example spreadsheet may be viewed at http://spreadsheets.google.com/ccc?k...WLMyhNJLiPLTfA.
I'm trying to use a variation of the COUNTIF function to generate a total number of occurrences. The RANGE is a singular column on each of five tabs in a spreadsheet, but the CRITERIA (hopefully) is made up of two separate cells on one tab of the same spreadsheet. Here is the formula as I have it written:
The purpose of this formula is to check to see how many times a student does not show up for an appointment over the course of their time with us. We have one tab with all the pertinent info on each student and I'd like to use this formula for a "No Show" column to more easily know when to cancel a student's services. The problem is that I can only get the function to search for one criteria right now. I've tried using the OR function and adding it to the current function but it always gives me 0 as the result, but if I take out the OR it returns me a higher number. Here's what it looks like with the OR added:
I am creating a spreadsheet for results of a survey. I am trying to give numeric values to text fields so that I can place the cell values later into a SUM function. The text fields are entered into the spreadsheet via a drop down list in each cell I created by utilizing the validate button. The text field contains choices like The text field contains choices like “1 Very poor,” or “4 Very much.” The source for the list is on a separate sheet.
EX: If cell C5 equals the choice “1 Very poor,” and cell C8 equals “4 Very much” I want the cells to have numeric values of 1 and 4 respectively so that I can utilize an equation like =SUM(6-C5+6-C8) later in the spreadsheet to calculate aggregate scores.
I have a client where I have agreed to a variable price per hour depending on how many hours is used.
So the first 40 hours of a month costs X USD The next 40 hours of a month costs Y USD The next 40 hours of a month costs Z USD The next 40 hours of a month costs Q USD And all hours above 160 in a month costs T USD
All hours are registered as decimal with 15 minutes as a minimum, so 0.25, 0.5, 0.75, 1, 1.25 etc.
Now the challenge for me is to set up a formula calculating the total cost based on how many hours is registered each month.
I.e. if I register 46 hours in one month the first 40 hours should be multiplied by X and the last 6 hours by Y. And if I register 173 the calculation looks like this:
40 * X 40 * Y 40 * Z 40 * Q 33 * T
And I can't figure out how to make the formula calculate based on what is between the various intervals.
I 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.
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.
I am trying to do a vlookup to 1 sheet based on customer number and return price. If no customer number than I want to do a vlookup based on dunnage number to return the price. This is what I have for a formula but it never returns a value if one is found in the first lookup.
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'm trying to create a excel sheet which will automatically return a price based on a given width and drop value. Currently, I calculate the price manually by looking in a price book which has plenty of rows and columns and prices. I want to simplify this by simply entering the dimensions so it automatically calculates price based on the dimensions entered.
Below is the start of my worksheet. If I choose the exact sizes shown on the table, it will return a price, however if I choose a size that is not listed, I get an #N/A. Eg. If I choose 780 x 1500, it will return the price $179, but if I choose for instance 775 x 1490, it only returns #N/A, when I want it to still return the price $179.
My formula in K3 is =IF(AND($I$3>0,$J$3>0),INDEX($A$3:$F$8,MATCh(J3,$A$3:$A$8,0),MATCH(I3,$A$3:$F$3,0)))
I have included a copy of the spreadsheet. What I need to do is get a cummulative duration report for each crew. This particular spreadsheet has less data than most.
Many times there are 8+ crews. I need to make a column or graph or report or something that will continually update as changes are made and report back something like; Sewer 142 hours, Water 200 hours etc...
I was recently posed with the following by our PMO: "Can I, or how do I, print only those tabs that belong to specific PM? I know I can go and select just those tabs collectively and print but I hate sifting through these 40+ tabs to find the ones that belong to PM "X". Can't you just use, or create, a macro or something?"
I replied that this might be limitation of Excel but that I would research it and see.
The project template cell reference is D5, there are 11 tabs containing project info for this PM (out of 43), it is running on XP, and is in Excel 2002.
I have an excel sheet with over 50thousand entries, each entry is identified by an ID code in the L column. I want a code that creates a new workbook with all entries having thesame ID code. if it's not too much, i want to have a pop-up window that asks me to enter the ID code.