I have a question on data interpolation with Excel 2007. Normally, this wouldn't be too much of an issue for me, but for some reason I cannot figure it out.
I'm working on an Income Statement, which is designed for 5 years. I have values 1 and 5, which are given to me (B7: 3,500,000 and F7: 5,200,000). I'm trying to figure out the other 3 years of revenue assuming a growth trend. The values should fill the series B7:F7.
i have a spreadsheet with a column of numbers in increments of 5 ie 25, 30, 35, 40, 45, 50, etc. and an adjacent column of decending numbers ie 275,273,262,250,238 where the numbers in column b decrease by varying amounts each time. for a given value say 37 i want to interpolate between the corisponding values of 35 and 40 to give an answer.
View 9 Replies View Relatedi'm trying to write a sheet that records weight from a date and user input
trouble is people are likely to miss days out and i want to fill in the missing dates weight based on actual inputs from other days. if i make it easier and say it like this column A is the weight, column b is the number of days gone
as an example i've made it straight forward but left 4 values out
A1616B161
A1515B152
A14B143
A1313B134
A1212B125
A1111B116
A10B107.....................
I am looking to write excel function that would be able to interpolate between two list of values. One list containing xValues and another yValues and a x value the specified for which the appropriate y value is to be returned. For the moment I have written a short function interPLinear into which I wish to feed an array and then simply add those together. This is so that I can make sure that my syntax is correct. Unfortunately I am struggling with that at the moment. My function looks like this.
Function InterPLinear(xValues() As Double)
Dim Sum As Double
InterPLinear = 0
InterPLinear = InterPLinear + xValues(1)
InterPLinear = InterPLinear + xValues(2)
InterPLinear = InterPLinear + xValues(3)
InterPLinear = InterPLinear + xValues(4)
InterPLinear = InterPLinear + xValues(5)
'I know that there will be only 5 values, will make it generic at a later stage.
End Function
In the spreadsheet I have created a short table with 5 rows and 1 column with values 1 to 5. In another cell I have entered the following =interplinear(A1:A5) and I have tried the following as well {=interplinear(A1:A5)} Could you perhaps tell me how I can correctly access this function so that it does not return #value
I have attached the file if it is needed
This is almost embarrassing, but I have a simple VLOOKUP function that I can't seem to figure out. On Sheet2 cell A11 is the value I want to look up in Sheet1. Sheet2 B12 is the cell I am entering the VLOOKUP function. The value I want entered is in column 7 of Sheet1. I hope I have explained this enough. My formula entered is =VLOOKUP(A11,Sheet1!$A$9:$A$42,7,FALSE).
For whatever reason this is not working, and I am getting a #REF! error.
I've got a spreadsheet with several rows and the numbers within those rows are totalled up at the bottom. Is there a way where i can highlight the cell within the spreadsheet and it takes it off the accumulative total at the bottom? or something similar to this effect? I just don't want to have to remove figures from this spreadsheet and highlighting will be a good way of seeing what has been done
View 1 Replies View RelatedI'm trying to see how accurate people's work predictions are to actual work completed. So I have these formulas:
=IF(C15=0,"",(SUMIF(L$24:IO$24,"Est.",L15:IO15))) - total estimated days
=IF(C15=0,"",(IFERROR((SUMIF(L$13:IO$13,"Act.",L15:IO15)),""))) - total actual days
But the problem of course is that people estimate a load of work and only fill in the actual days as they go along, so the accuracy of comparing one to another is almost always misleading.
What I want to do is only count the values in the weeks Estimated if the Actual figure is also there (L24:IO24), which is always the cell directly on its right.
I learnt that " " & can combine the text and figure. However, when it comes to a negative numbers, i dont know how to maneouver it. Eg, Cell A1 is USD(50,000.00) and the comment in Cell B1 is Cost is USD(50,000.00). So the function in B1 = "Cost is "& what to put here?
View 3 Replies View RelatedI am having a bit of a problem creating a formula for this report that I have to update every week. I need to be able to find the room nights and net room revenue values for the specific rate code in the "MATRIX" workbook.
On "CURRENT REPORT," I need D4 to pull up the value on "MATRIX" that equals "Room Nights" in column H and "CONABC" in column J. The same thing needs to happen for F4 but with "Net Room Revenue" in column H. The full report has about 500 of these codes in column J, and I need a formula that I can copy easily and will not be affected if codes are added or removed. This is super last minute - I need to finish this report by tomorrow morning, so take a look at the attachments.
I just have a quick question with regards to two-way interpolation of data.
I have a data table which has x-values, y-values and z-values.
I need to obtain an interpolate value for z, given a value for x and a value for y.
The x value specified is 1.569 and the y value specified is 1.66.
I know how to use the FORECAST formula for a simple x, y table, but I have no idea what to do here when I need to find a z value, given the x and y values.
I have data essentially comprising a set of points with x and y coordinates. Is there a way to index and interpolate between points?
I can use INDEX or LOOKUP to look up a y value fo a valid x value but don't know a way to interpolate if the value is between x values. Is this possible?
I got a table of two columns (A1:B6). One column (A1:A6) got temperature values and the other column (B1:B6) got enthalpy values as a function of temperature values as shown in the attached file. All values in the table are empirical values and I am trying to use them to obtain the enthaly (B11:B21) for my own temperatures (A11:A21).
I can use VLookup formula to do so but it won't be accurate because the enthalpy values are very sensitive to temperature changes. I need to use table A1:B6 and interpolate for temperature values that are located in between the integer temperature valuse. e.g. if the temperature=1.5 (between 1 and 2 in the table), how can I obtain the enthalpy value=15 (between 10 and 20) using the table.
a formula that would interpolate the volume in the below worksheet....
View 9 Replies View RelatedI have an array of data like below:
1
3
5
7
9
I need that array interpolated to say 20 data points but the first and 20th data point must be the actual first and last data point of the array (1 & 9 in the example). I have downloaded the xlfun software but the interpolat function only does 1 point at a time...than as you drag the function down it changes the initial array. Of course I realy have anywhere from 40-60 data points to be interpolated to 100.
With a known X and Y, trying to solve for Z from a table. ie: IF a variable X is defined across a row, another variable Y defined down a column, the data field Z fills in-between. What do I need to use to interpolate for both X and Y to solve for Z?
View 9 Replies View RelatedI have pasted 5 fields with some random values that are in A1 to E1. E is the column I am trying to populate to fill in the missing data for C.
I have written a function that will interpolate between 2 deltas using the dates. Assuming that June 09 will always have a corresponding delta (e.g. here 33) how can i populate the INTERP column using my YINT function. I can do this manually 1 at a time, but the already-filled cells in the Delta column will not always be the same and manually would take ages....
I was using the macro provided by shg in post#6 of this thread: Interpolate Two-dimensional Array? ...
View 6 Replies View RelatedActually, I've created the user defined function (UDF) to interpolate (both linear and bilinear). It's just, I keep getting this annoying error that says "A value used in the formula is of the wrong data type." But here's the kicker...I converted the UDF into a subroutine for trouble shooting, and I was able to step through the entire code and get the correct output.
Function itcinter(efpd As Single, pwr As Single) As Variant
Dim rnge, mtrnge As Range
Dim w, x, y, z, xx, yy, b As Single
Dim scenario, a As Integer
Dim J As Variant
scenario = Worksheets("Input").Range("B1").Value
pwr = pwr / 100#
If (scenario = 1) Then
Worksheets("ITC").Select
'Make table into a range for VLookUp
Set rnge = Worksheets("ITC").Range("A3", [A3].End(xlDown).End(xlToRight))
Set mtrnge = Worksheets("ITC").Range("A3", [A3].End(xlDown))
'If the given value does not match a table value exactly
On Error Resume Next.......................
I have 5 worksheets each with tables that are formatted the same on each sheet. I have named each table as a named range (ex:filter5tsd15BSF). I have a userdefined function that interpolates values for a single table in a vba module. I can make this function work for a single table by calling the function as =Linterp(filter5tsd15BSF,C12) so I know the function is OK. However, I really rather program this some way that all the tables get passed to the function and then a select case for the conditions for which filter and which TSD (15 or 25) will then select the correct table.
View 2 Replies View RelatedAttached is my sample data...Indicative Data_Revenue 2.xlsx
My aim is to populate columns C to G (Q1,Q2,Q3,Q4) automatically via vba against a particular account name(in expected output tab).The account names will have a Key word (listed in the "List Of Account" tab)The Q1 Sum should be a sum of Q1 revenue against all such accounts containing the Key word. Same applies for Q2,Q3 and Q4.Each account has 3 types. viz, BAU, Top Commits and Strong Prospect.
The BAU quarter sum should be pulled from "Revenue 1 - Assured" tab where Q1=Apr+May+Jun, Q2 = Jul+Aug Sep and so on. The Top Commits Sum should be pulled from "Revenue 2 - Expected" against all records where "Sales Stage" Column equals Stage 4. The Strong Prospect Sum should be pulled from "Revenue 2 - Expected" against all records where "Sales Stage" Column equals Stage
Currently I am using formulas but there is too much of hard-coding and its becoming difficult to manage as Accounts increase.
I own a local restaurant and want to streamline my excel spreadsheets. Currently, I have yearly workbooks (2005, 2006, 2007, etc.) that have all of my revenues included in them, split up monthly by worksheets. I then have lines for each day within the month for the revenues earned at that day.
So, if I want to find how much revenue I earned on March 1st, 2006, I go to my 2006 book, March worksheet, and look at the 1st. Here's where my problem lies.
I want to create a new spreadsheet where all I have to do is type in a certain date (say March 1st, 2006) and all of the information for the date will be automatically displayed on the new sheet.
I am trying to figure how to get a formula to work that will spread revenue number over a four month period. The dates will be dynamic so they will change but the spread stays the same.
I have attached a snapshot of what I am trying to do.
I have a table which shows the monthly revenue of Company ABC , by client / by location / by business line.
ClientCountryBusiness LineJan-10 Feb-10Mar-10A001SingaporeResidential$ 50,000.00 $ 23,333.00 $ 54,115.00 D003SingaporeRetail$ 50,000.00 $ 21,548.00 $ 54,654.00A001SingaporeCommercial$ 63,321.00 $ 75,542.00 $ 21,564.00 D003AustraliaResidential$ 26,564.00 $ 50,000.00 $ 45,654.00 G002AustraliaRetail$ 50,000.00 $ 21,546.00$ 63,321.00G002AustraliaCommercial$ 26,602.00 $ 65,341.00 $ 24,568.00 A001Hong KongResidential$ 33,565.00 $ 26,564.00 $ 64,454.00 G002Hong KongRetail$ 54,232.00 $ 50,000.00 $ 15,454.00 D003Hong KongCommercial$ 26,564.00 $ 21,564.00 $ 23,333.00
If I want to calculate the total revenue for the month of January based on the following variable criteria:
Criteria
Client: A001
Country: Singapore
Month: Jan-10
I can achieve that with the use of SUMPRODUCT. However next month, I will then have to manually change the SUMPRODUCT formula so that it will extract data from the Feb column instead of the Jan column.
Is there a formula which will not require me to change the formula every mth? Ie. I can get my answer simply by changing the criteria?
I made 2 sheets: the first one contains the database and in the second one i want to analyze it. Now i am looking for a special sum- function. I want to sum total revenue for a particular company (criteria 1), in a filtered country (criteria 2), month (criteria 3) and class (criteria 4). I'd like to first execute the three filters (country, month and class) and then be able to total revenue of that particular company. Note that after the filters still several companies are visible in the database. Does anyone know how to calcalate this in cell B9:B12 (Analyze sheet) of my attached file.
View 9 Replies View RelatedI have sales data for Clients that has the client name month and year(combined for date) and revenue for each month. I would like to take all clients in a given month and see the average revenue per client and then be able to display that in a bar chart by month. I also have the data loaded into a PowerPivot Data Model.
View 1 Replies View RelatedI have a deferred revenue model. Revenue from each month earns out at an equal rate over twelve months. Sales for each month are in column B (B21) and C. I created formulas for the first year but when I get to the second year, I'm not sure what to do, because the earned premium will include values from previous year sales and current year sales.
View 4 Replies View RelatedI have to calculate the flat revenue split by month between 2 different dates. The two dates may be in the same year or different years.
excel formula for the same.
Is it possible to do the same in pivot?
Any way to set up Solver in order to maximize revenue, but with the constraints equaling ONLY one of two values (e.g. 0 or 100)?
View 1 Replies View RelatedSo here's the setup: A customer purchases a service which lasts 6-months. As such, revenue recognition for the company can only occur as the service is provided. So if a person orders a $600 service at the beginning of a month, revenue will be recognized at $100 for the next 6 months. I'm trying to create an Excel Waterfall chart which will show the monthly revenue recognition amounts for all orders depending on the date which they were ordered.
I've attached a sample of what I'm looking to do. It's become tricky for me because Revenue Recognition is pro-rated based on the date ordered (i.e. order on the 20th of a month so at the end of the month 1/3 of the month is recognized as revenue). I'm looking for something that will populate the percentage of the order amount, each month, that will be recognized. I've populated what row 3 should be, but there doesn't seem to be an easy way to apply a formula or something across all cells.
I need to work out Revenue Rank & Year to Date calculations.
Consider a simple table:
| Partner Name | Year | Month | Revenue |
------------------------------------------
| John Smith | 2008 | Nov | 2000 |
| John Smith | 2008 | Dec | 2200 |
| John Smith | 2009 | Jan | 1898 |
| Mary Smith | 2008 | Nov | 1767 |
| Mary Smith | 2008 | Dec | 1867 |
| Mary Smith | 2009 | Jan | 1953 |
------------------------------------------
etc..etc...
I'm not using Pivot Tables since there are more complex issues around presentation which are preventing me doing this so are using good old formulas..
Revenue Rank is in reference to the Partner in this case. I need to be able to say John Smith is rank x out of xx by summing up his revenues for both:
a) one month
b) a range of 3 months back
How do I work this out? Especially the date calculations when I just have a year and month in separate fields?