Ive been given the data for bond pricing for two tenures i.e. 15 years and 20 years . I needed to find the average pricing for 16.5 years , whether excel is able to compute such pricing?
View 5 RepliesLooking for a formula to calculate 5 yr avg deviation.
year # of event
2007 59
2008 63
2009 53
2010 42
2011 46
with this set of numbers I can figure out the formula for 5 yr avg but can't figure out how to arrive @ the -18.3%.
where 5 yr avg is 52.6
where 5 yr avg deviation is -18.3%
I have a list of people with 10 years of salary history for each (in ten consecutive columns on the spreadsheet).
I need to calculate the HIGHEST 3 consecutive year average salary for each (if they have less than 3 years with salary, then it should just average the years the do have, be it 1 or 2 years).
Here is the kicker: some people have breaks in service (for these years, there is a blank in that entry). These years should be ignored and skipped in calculating the avergaes.
So if someone had salary figures in years 1, 2, and 4, but a blank in year three, the average of years 1, 2, and 4 would constitute one three year average (whether or not it is the highest is a whole other matter...).
I have been round-and-round the best way of doing this. I was thinking of maybe creating a UDF that calculates a three average, then do it up to 8 times (one for each starting year) in 8 "helper columns", and taking the highest average.
I have the following:
A2= 01/20/2000 B2= 02/05/2000
A3= 09/05/2001 B3= 10/15/2008
C2 and C3 is where the answer will be.
what formula can be used to find out how many January 31th has passed between those ranges of years?
I have a very complicated vlookup problem, at least for me. What I want to do is to find values from different firms in different years, by just changing the name of the firm in the formula. This is probably difficult to understand, but please refer to the excel sheet added.
View 4 Replies View RelatedI am trying to capture the number of years/quarters are between two dates - giving credit for one day worked into the quarter.
In the attached png file you can see the formula I used for column J. This is the same formula I need to use for Column R but now using 10/1/11 and the date provided in Column R. The formula should be used in Column S "Part B" so that I can determine the number of years between 10/1/11 and the date in Column S.
I have a date 07/28/2027 and need Excel to calculate a date 65 years in the future taking into account leap years.
View 9 Replies View RelatedWhen i open a new thread, so here i am doing so at the advice of him. The message below is specific to him to reply to but for anyone else that might want to help me with this set of formulas to resolve my worksheet headache and lack of skills in excel. Please feel free to have a look at the worksheet i posted and the comments in each color coded cell.
View 8 Replies View Related I have a zone matrix I'm working on. How can I have a formula match the state and return the numeric value assigned to the state?
Say I enter WA in cell "G5" and in column A I have the states and column B has the numeric value assigned. I need to to match and return the value.
So if "OR" is entered the returned value is "1"
StateZone
WA1
OR1
ID1
[Code]....
I receive hundreds of part numbers to quote daily. I send out RFQ's to my suppliers and they respond with pricing. I gather all the data into a similar excel sheet below and assign the wining supplier.
in automating a task that will compare the group of data for best pricing and populate a "WIN" code in column "A" if that supplier was chosen for the that qty pricing.
Here is the excel example.
******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutI22=ABCDEFGHI1Scenario #1 - Supplier chosen for best pricing based on column "E" 2WINNERPART#VEN IDBRK1COST1BRK2COST2BRK3COST33 123ALL90110510107.54 123FO3511258107.755 123RO501958.51086WIN123AR651858.751087 8 9Scenario #2 - Supplier chosen for best pricing based on column "G" 10WINNERPART#VEN IDBRK1COST1BRK2COST2BRK3COST311 123ALL90110510107.512 123FO3511258107.7513WIN123RO501958.510814 123AR651858.7510815 16 17Scenario #3 - Supplier chosen for best pricing based on column "I" 18WINNERPART#VEN IDBRK1COST1BRK2COST2BRK3COST319WIN123ALL90110510107.520 123FO3511258107.7521 123RO501958.510822 123AR651858.75108Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have a worksheet that needs customers split into different price bands
here we go
I have 13 customers all from the same group (a)
1 customer in group (b)
1 Customer in group (c)
& 80 independant customers (d) worksheet only has 9 (1,2,3,4,5,6,7,8,9)
Column a is a list
D17 is the drop down list
D21 is the starting price
C2:D5 is the pricing multiple to be applied
What I am trying to achieve is when an independant company is chosen it defaults to d for pricing.
I have a spreadsheet of prices from vendors for products that my company sells that is updated daily. The sheet contains several products with pricing from multiple vendors and multiple locations. (Example: paper, pens and staples sold by Staples, Office Max and Office Depot, out of NY, NJ and CT.) Many of our customers use a set vendor for most days but occasionally call us to see if we can beat their regular vendor's price. We would like to be able to email them with our pricing every day so that we can have their business every time our pricing is best without them having to call us.
We have a sheet of customers with contact info and common products purchased put together already for a mail merge. At this point, we manually enter the best price from our vendors and a column calculates the price that would go to our customer based on markup and taxes, etc. and we send it out using the mail merge tool. However, our pricing sheet is not ready until about 8 am and the mail merge sheet is not ready until at least 10 am due to the large (and growing) number of customers on it. Many of our customers place their orders first thing the morning so we're losing business.
way to use VLOOKUP to draw from the pricing sheet as soon as it's finished and copied into Sheet2 of the mail merge sheet. The problem is that each customer has different parameters. We are in New England so we have vendors in several states. Ideally, we need to be able to provide a price for a RI customer based on the lowest price for their product out of one of our RI vendors and provide a price for an MA customer based on the lowest price for their product from any vendor in any NE state. The lowest price is different every day and the locations all have different prices. I'm also a VLOOKUP newbie so I don't know everything that it can do. Currently, I only use it to calculate shipping based on a pricing sheet that is always the same. I choose the product and all other data from the static price sheet comes over. I'm not sure how to create a sheet using VLOOKUP for a sheet that changes every day or one that requires more than one drop down. I'm not sure how to create the second drop down.
Also, at this point, the mail merge generates multiple emails for customers that purchase 2 or more products from us. Is there a way to include an array in a mail merge so that their email contains all products they use?
I'm not sure if it makes a difference, but each product is not sold out of each location and each vendor so the number of lines per product is different for each product. However, the number of lines per product is always the same day after day and if any vendors or locations are added or removed, we can tweak our merge accordingly. So basically the first 25 rows are always for product A and column A always reads Product A for rows 2-26; the columns that change are the vendor and location columns and of course the price column.
i have many tables for many carriers with the same format, and i need to create a unified table of them, the tables look like this
-------A---------------------B-----------------------C---------
----Destination----------Country_Code-----------Price_Per_Minute
1------UK-------------------4479-------------------0.256-----
2------USA------------------1148------------------0.300------
3------Jordan----------------9626------------------0.899------
4------India------------------9198------------------0.345------
so let us consider that these are the prices for X company and i have another ten tables for other 10 companies, what i need to do is to create a unified table that includes all the companies prices for all the destination depending on the Country_Code as it could be considered as a primary Key and without any duplications and to include the all the data in all the tables as some destinations and prices are not available in all the tables, so the tables contains different data, the unified table should look something like this
-------A----------------B----------------C------------D---------E------
----Destination-------Country_Code-----X Price-----Y Price----Z Price----
1------UK-----------------4479-----------0.256-----0.254------0.356----
2------USA---------------1148------------0.300-----0.876------ ---
3------Jordan-------------9626-----------0.899------0.987------0.324---
4------India---------------9198-----------0.345----- -------0.564--
The phone call is charged at 0.57 for the first 60 seconds, thereafter 0.0095 per second. Determine cost if duration (in seconds) is available?
AND
The phone caal is charged at 1.654 for the first 60 seconds, thereafter 0.827 for every 30 seconds.Determine cost if duration (in seconds) is available?
We are trying to work out a pricing model based on how many units we will produce. In production the price per unit will be really high when you produce a low quanity, and the price per unit will be lower with the more units you produce (but never reach zero). What we are trying to accomplish is to create a formula to build a pricing structure that accounts for this curve without having to price every possible scenario.
For example, if we have the following data where for 1 unit we charge $3 per unit to produce, for 5 units we charge $2 per units to produce, etc...:
1 units @ $3 per unit
5 units @ $2 per unit
10 units @ $1 per unit
What I need to figure out is how much it would cost per unit if we were to produce 3 units. I'm guessing it would be somewhere around the $2.40 mark give or take. Or say 8 units, would be something like $1.50 per unit.
Is there a formula that can calculate this? I've tried thinking of it as a moving average, or prorating the pricing.
I have attached a sample workbook, (Pricing Sheet - Major) within this work book there is a worksheet entitled Price Book which has commonly-used materials, unit prices and labour rates. What he wishes to do is use the contents of the Price Book worksheet within a worksheet called Pricing Sheet to enable him to prepare quotations.
I had the idea to first sort the items in the Price Book worksheet and then produce a dropdown list of these items for use within the Pricing Sheet worksheet and used the VLOOKUP function to obtain the values for unit prices and labour within the Price Book and use them in the Pricing Sheet worksheet.
I have 3 excel sheets (A,B and C) linked to a master sheet (X). In each sheet (A,B and C), people enter unique idetifier in first column, its type (mr, ab, J) in the second column, start date in the third column and finish date in the fourth column. At the end of every week, i subtract each start date from the finish date and get the number of days spent for each unique idetifier (column N). I preiously posted a thread (Calculate difference between dates excluding weekends) to inquire about how to calculate the difference between finish date and start date and exclude the weekends. I received some nice solutions, but none has worked as the start date cane be weekend as well.
My second and major query is once i have the difference in weekedays for each unique identifier in all the three sheets, I want to calcuate the average of each identifier type (mr, ab and J) in the master sheet. In the master sheet, i have used sumproduct to calculate the average of each idetifier type from column N for each sheet. That means i have a column for A sheet, subdivided into three types, which include the average. I am using sumproduct for this. Once this is done for each sheet type, i need a final colum in the master sheet that calculates average of all mr from A, B and C together, and similarly for ab and J. The problem here is if i combine all the sumproduct formula, i am not gettng the right answer.
My formula looks like SUMPRODUCT((SheetA!$E$5:$E$40="mr")*(SheetA!$M$5:$M$40)) for all mr in sheet A. I then divide this by SUMPRODUCT((SheetA!$E$5:$E$40="mr")*(SheetA!$M$5:$M$40<>"")) to get the average of all mrs in sheet A. I do the same for sheet B and sheet C for all three identifier types. Now i want to combine the formula for all sheets together under mr, Ab and J. I thought the following should work ((SUMPRODUCT((SheetA!$E$5:$E$40="mr")*(SheetA!$M$5:$M$40))+(SUMPRODUCT((SheetB!$E$5:$E$40="mr")*(She etB!$M$5:$M$40))+(SUMPRODUCT((SheetC!$E$5:$E$40="mr")*(SheetC!$M$5:$M$40)))/((SUMPRODUCT((SheetA!$E$5:$E$40="mr")*(SheetA!$M$5:$M$40<>""))+((SUMPRODUCT((SheetB!$E$5:$E$40="mr") *(SheetB!$M$5:$M$40<>""))+((SUMPRODUCT((SheetC!$E$5:$E$40="mr")*(SheetC!$M$5:$M$40<>""))).....
But this is giving me wrong value. It is working fine when done for individual sheets, but when combined for all three sheets together, the value is wrong.
Column B: The day of the week that the date is, Sunday, Monday, etc.
Column C: Every date in 2008
Column D: my data (the number to be averaged)
What I need the formula to do:
Look in Column B for every instance of "Sunday", and count them up (they count only if the data in column D is not 0) and use that number to average the number in column D
So if I have 52 Sundays and we are only 4 weeks into the year and the 4 data points are 50, 100, 75, 25. The average will be 62.5 instead of 4.8 (divided by 52).
I am trying to figure out how to take the average of the last 4 "non-zero" numbers in a series of values.
The sheet looks something like this, numbers are all in one row:
7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32 , 7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32
I'm looking for a formula that will look at all the last few totals (starting from the end) and take an average of the last 4 totals that were > 0. In this case it would average {32, 32, 28, 32} ignoring the 0 total.
Attached Dummy file. I would like to be able to get the average time spent on a process for the day recorded on the "Results" tab. I have posted the answers it should be on the Results tab, which was done manually. I would like the file to do it on it's own. On the individual's tab, the time and instances will be recorded for each job. I believe I am close, and the only thing I need is the Results page to be able to return the desired results. Ranges varies day-to-day, and I think (maybe), I can figure out the dynamic range settings.
I would like to avoid creating a helper column for each set, because then I would be dependent on the person expanding on this. I am also avoiding Pivot Tables, I understand it, but not the ones that needs it. This will have about 15 tabs for employees, and could be possible that more or less will happen, depending on workload. This will continue to be manual for now.
I have a total in F16 that i need to find an average of.
The range is set from f4 - f15 (12 months) and need to find the running average per month. but as we only have done 5 months of the year so far that is the only average required (F16/5).
I need this to count at the end of june (F16/6) basically not counting any zero months (jul, aug, sept etc until the end of each)
I am just looking for an function which will gives me the average of the hours worked on daily basis. A breif descripiton is i have an workbook in sheet1 i have the sample report which i struggle to prepare on daily basis for each client.
Sheet2 i have the Raw data in column H i have the client name and then in column I is Date and finally in J the hours we worked on in it. To say i just want the average if the each client on the particular date as shown in Report sheet.
I want to find the average of a range.
I've put the range in order and I want to take off the top 7.5% and the bottom 7.5%. Then find the average of the data that is left. (the middle 85%)
Can I do this with a formula?
I want to find incremental average
My cell numbers are
AG4
AG4,AG13
AG4,AG13,AG22
AG4,AG13,AG22,AG31
and so on..
(In incremental of 9)
I have the following Data:
Full Drum
247
Full Drum
247
Full Drum
261
Full Drum
261
[code]....
I need to know how to find the best combination of combining these drums to get the highest average from each combination. Each combo will have 3 drums except for one which will have two and a half.
In the table below I'm trying to work how trend is being produced but I can't figure it out.
I have found how to find the moving average =AVERAGE(C2:C5)
I've tried a few methods using the trend function but I really don't understand what I'm doing. how its being calculated by looking at the table?
In column A is the staff name, column B is the start date, column C is the end date and column D is the difference.
I need to find the average number days for task completed for each staff for the week. I need a formula that will look for a particular staff name in column A ( which appears many time randomly in column A for each day of the week) and calculate the average days for the total task completed for the week.
Let's say I had the following data ...
47
50
50
50
44
50
46
50
Basically, I have 4 numbers, out of 50, as seen above...I want it so the last number (46 in this case) is special; what I mean by that is I want the following conditions to be met.
--If the special number is the lowest, just take the average of the four numbers, and divide it by 50
--If there is a (are) number(s) lower than the special number, then I want it to locate the smallest
---The smallest number should then be "replaced" by the special number (for calculations purposes only, not in the actual table)
So, in this case, since the special number (46) is not the lowest, excel would locate the lowest number (44), and calculate the average of 47, 50, 46, and 46 (the 44 was replaced by 46).
I have two columns: 1) Last Date of each month from 2005 and 2) Value.
Date format: dd.mm.yy
For example:
31.01.05 1.7
28.02.05 -0.7
31.03.05 2.6
30.04.05 0.4
....
How can I find the average of second column of january months only? I tried countif, sumif and many others, but cannot find the solution.
I have attached an excel file. What I would like to find is the average of column D (number) with respect to column A (Genotype ) and also different wave length (Column C). i.e.:
Genotype 1 to its corresponding value in column D for wave length 450
Genotype 1 to its corresponding value in column D and for Wave length 470 to and so on.
Attached File : test2.xlsx‎