# Determine The Percentage Rate Of Change In A Trendline

Feb 13, 2009
I'm trying to determine the percentage rate of change in a trendline. I've got a sales chart with 90 days worth of sales figures. I can see that the trendline is trending down, but I'm not sure exactly how much.

If you take the first data point in a trendline and subtract it from the last data point and then divide that by the first it should give you the percent change. Excel must be able to do that math for you but I can't even figure it out to save my life. I can't even figure out how to display the first and last data point much less the "rate" of change I'm looking for.

View 9 Replies
ADVERTISEMENT
Feb 13, 2014

From a chart in Excel I need to automatically calculate what the annual percentage growth rate is of a trend line. How to automate this in Excel? I've attached a sample so you can see what I'm trying to accomplish.

View 6 Replies
View Related
Jan 12, 2014

I'm doing an exercise for school and I'm totally confused here. I have to:

With the original assumptions, goal seek to determine what the Annual Rate Increase would be for the total expenses to be $175,000 (answer = 20.77%). Here's what I have: through the process?

View 1 Replies
View Related
Feb 13, 2010

I am trying to calculate APR (Annual Percentage Rate) for a mortgage loan that has a balloon feature. I have tried to the the RATE function but it only gives me the APR for a loan that is ammortized over 30 yrs and paid in 30 years. I need the APR for a loan that is ammortized for 30 years with a baloon in 5 years.

View 9 Replies
View Related
Jun 6, 2014

What I need is a worksheet which will generate fields from a set of variables (similar to a loan amortization schedule).

I don't know how to write the field code to include the annual percentage rate increase based on that additional input variable.

Input variables would be:

starting rate: (ex: 8Â¢/kilowatt hour)

starting date: (ex: July 2013)

average kw hours/month: (ex: 1062.5 kw hours)

annual rate increase: (ex: 3% *default 0%)

years to display: (ex: 25 years, *shown in months in fields below)

service months per year: (ex: 12 *default 12)

[code].....

So, data fields below the variables would be blank until these are entered.

Assume I would protect the sheet to avoid messing it up.

this would generate a chart showing rates over time from the fields below.

Also it would generate fields in a summary area to show the rate per year for future years.

Secondary, if there were a way to input actual usage and rates from specific months in the past, that would be useful but not part of this forecast model.

The goal of this model is to forecast electrical costs for residential power users compared to independent solar power generation to 25 years forward.

I want to make this because I am skeptical of the advertised savings that solar installers use to convince clients that their return on investment is so high. I believe their data to be greatly inflated projections.

Using this in comparison with other data such as inflation and wages will allow me to get a very good economic picture of the future of independent solar energy generation and its impact on the average home owner.

View 2 Replies
View Related
Aug 24, 2014

Data is;

83300 - hypothetical number of times I have fired my gun at target.

43209 - hypothetical number of times I have scored bullseye.

So, my bullseye percentage = 43209/83300, or ~51.87%.

Need formula to determine how many more consecutive bullseyes I need to shoot, in order to achieve 70% ratio.

Since each shot from now on will be a bullseye, both values (hits & shots) will increment together.

View 5 Replies
View Related
Mar 31, 2009

As you can see in the attached sample file, I have different currencies, that all need to be converted to USD.

The exchange rate are displayed on sheet 2.

Currently the formula just picks all values without considering the exchange rate to USD. What I need to have, is a formula which, based on the currency, picks the correct exchange rate and outputs USD.

Apart from that, another thing the formula should consider is the value of G1 in sheet 2. With G1, the user can later define a specific type to be calculated by simply pressing down the button and chose either FG or RM.

The formula needs to:

Find out what currency the value is in and automatically convert it to USD via the exchange rate values. It also needs to consider the value of G1 in sheet 2 in order to filter out the other values. If, for example G1 is FG, then only FG should be taken into the process and therefore only values where type FG is marked.

View 9 Replies
View Related
Dec 21, 2009

I have a financial sensitivity sheet setup by year, where for example below each year is a cell you can change the exchange rate in and it effects the outcome of cash flow as these cells are linked to part of the finances. Then I have a "reset" button setup that is assigned to a macro. When you click the reset button all cells in the exchange rate row will be changed to a value that is entered in a "base case" cell. That way various years can be changed but also everything can be set back to the default or base case. My macro for that case is this:

View 5 Replies
View Related
Feb 22, 2009

I have wave data and need to extract out of a column the greatest rate of change i.e. start from a1 if the numbers are going down find the first number that starts going upward...and put in column b...then find the next first number that is going down and put in column b....and so on and so on all the way down the column.

View 4 Replies
View Related
Jun 4, 2007

The formula I am looking for would tell me what annual growth rate % I would need to achieve to make any investment reach a set target, for instance, what % of fixed annual growth would I need to make 200K grow to 750k in say 10 yrs or any time scale. I was given the formula below but Excel tells me it's wrong, I have tried putting 10 before ^ and the 10 after but to no avail, could some kind soul please put me straight.

r = 100((Y/X)^(1/n))-1)

So for X = 200, Y = 750, n = 10, we have

r = 100((750/200)^(0.1))-1) = 14.1309%

View 3 Replies
View Related
Nov 27, 2013

i have the formula below,

currently the output is comming as a number but i need the output to come has a percentage what adjustments do i need to make to do this?

=CHAR(149)&" "&"The overall YTD"&" "&"performance is"&('Red Activity & Performance'!R45)*100&IF(INDEX('Red Activity & Performance'!R45:R55,MATCH("Red 1 8min",'Red Activity & Performance'!$A$45:$A$55,0),MATCH("YTD",'Red Activity & Performance'!$R$44,0))<INDEX('Red Activity & Performance'!$C$45:$C$55,MATCH("Red 1 8min",'Red Activity & Performance'!$A$45:$A$55,0)),"Is below the target (75%) by: "&TEXT(INDEX('Red Activity & Performance'!$A$45:$A$55,MATCH("Red 1 8min",'Red Activity & Performance'!$A$45:$A$55,0))-INDEX('Red Activity & Performance'!$R$44,MATCH("Red 1 8min",'Red Activity & Performance'!$A$45:$A$55,0),MATCH("YTD",'Red Activity & Performance'!$R$44,0)),"0%"),"has achieved the national target (75%)")

View 5 Replies
View Related
Nov 18, 2008

I have 26 data points, and I want to be able to change those data points by a certain percentage, defined by a separate single Cell value

Now here's the tricky part: I want some data points to go up by the defined percentage, and some data points to come down by the defined percentage. Which data point goes up and which comes down, can be totally random.

Is there a way of doing this by means of a formula, or is it a VBA job?

View 9 Replies
View Related
Feb 27, 2013

Pretend their are two Tabs in excel.

Tab 1:

"A1" contains a random number: "20"

Tab 2:

Column "A" has tonnes of random numbers:

6000 - Starting Balance (changeable)

5890

5800

5950

6080

6100

6180

6250

6300

an so on... (1,000 rows)

Based upon the number "20" that I placed in A1 on Tab 1, I want a formula that looks at the first 20 rows in column A within Tab 1, and then calculate the percentage change between the extreme "peak to trough" of the highest number to the lowest number within the 20 rows...

Below should make it really simple:

6000 - Starting Balance (freely changeable)

5890 -1.83%

5800 -3.33% - This was the lowest percentage change from the starting balance of 6,000

5950 -0.83%

6080 +1.33%

6100 +1.67%

6180 +3.00%

6250 +4.17%

6300 +5.00%

View 3 Replies
View Related
Dec 1, 2013

In the first picture the Pivot Table is showing the profit of each city in 2010 and 2012. Where I used Sort & Filter Largest to smallest, in order to display the cities with greatest profit.

Prosent.png

But when I switch the values to `% of` year (Previous). I get the change from 2010 to 2012 in percentage. But my PROBLEM is when I want to Sort & Filter from Largest to Smallest, cause I want to show the cities that have had the greatest percentage change from 2010 to 2012. But the I only get the same cities, and when I look at ex. 'Gjovik' is has had a 125 % in change and should be in the top.

So how am I able to Sort & Filter from Largest to Smallest the cities with greatest change in percentage?

View 3 Replies
View Related
Jan 8, 2009

Good afternoon Gentlemen, I have a column of data, with a varying number of percentage values that add up to 100%, separated by "NA", i.e.

33%

33%

33%

NA

25%

25%

25%

25%

NA

100%

NA

20%

20%

20%

20%

20%

NA

Now... when I change one of the values I would like the others to even up, i.e. in the last example if I change a 20% to 50% I would like the others to change to 10%... any ideas?

View 11 Replies
View Related
Jul 2, 2012

I was quite a few problems using worksheetfunction.ln() to calculate % change. I decided to write more basic code to try to accomplish this but I'm getting an overflow error.

Code:

DRows = WorksheetFunction.CountA(Sheets("INFO").Range("A:A"))

For r = 1 To (DRows - 1) '***** -1 is bc of ln property

percentchange = 0

newval1 = Worksheets("INFO").Cells(1 + r, 2).Value

old1 = Worksheets("INFO").Cells(1 + r, 2).Value

percentchange = (newval1 - old1) / old1

Worksheets("info").Cells(2 + r, 3).Value = percentchange

Next r

View 6 Replies
View Related
Jul 31, 2006

I am not sure why the html maker didn't work so I will try and ask this without it. Please be patient. If it wasn't so confusing for me I would have figured this out already so I come to you, the experts.

I have three sheets in a work book. I am attempting to track not only the differences but the percentage of change between data on sheets 2 & 3.

Here are the cells on sheet one:

A1: Name1 B1: Place1 C1: Difference1 D1: Percentage of Change1

A2: Name2 B2: Place2 C2: Difference2 D2: Percentage of Change2

This goes goen the page thru A42....

View 4 Replies
View Related
Jan 22, 2009

I am using Excel 2007. I have two sets of data. y-series is 58.61, 58.66, 58.71 and 58.76. x-series is 0.8313, 0.8309, 0.8305 and 0.8301.

I am trying to fit a quadratic function to this data. When I plot a curve and fit a trendline I get the coefficients for the equation ax^2+bx+c

a=1.3939e-11

b=-125

c=162.5225

The plotted line seems to fit the data fairly well.

However, I want to use the linest function and I am using the formula:

=INDEX(LINEST(B2:B5,A2:A5^{1,2}),1,1)

I change the index column number as appropriate to get the coefficients:

a=-75.23775

b=0

c=110.603763

The coefficients from linest seem to be way out. how I can get the output from linest to be the same as the trendline?

View 4 Replies
View Related
Jul 14, 2012

I need to change the backcolour of some textboxes on a userform that is populated with percentage figures from a sheet, ie:

10% (a positive percentage) textbox backcolour = red

-10% (a negative percentage) textbox backcolour = green

Below is some code showing my attempts up to now, but obviously I can't make it work.

VB:

Private Sub Textbox1_Change()

If Me.Textbox1.Value < 0 Then

Me.Textbox1.BackColor = vbGreen

Else

Me.Textbox1.BackColor = vbRed

End If

End Sub

View 5 Replies
View Related
Dec 29, 2013

I have created a scatterplot with a trendline and I need a formula to represent the trendline so it can be applied to separate results. How I can create a formula and an R2 value?

View 1 Replies
View Related
Apr 7, 2012

I tried to record setting a trendline to 100% transparent but this action doesn't seem to record.

View 2 Replies
View Related
Dec 18, 2013

I have a Pivot Table with fields for months and weeks. I also have a "Show Values as % Difference Field" that shows monthly or weekly % change. When I collapse the fields so that it goes from weekly to monthly (or vice versa), I have to manually change each Show Values As % Difference column. Is there a way to do this automatically or quickly?

View 1 Replies
View Related
Mar 17, 2012

I am trying to add columns to my Excel 2010 Pivot Table to calculate % change of sales from year to year. The field name is "Year", while the item columns in the field are 2007, 2008, 2009, 2010, and 2011. How do I add a column between the years that will calculate the % change os sales up or down from the previous year?

View 2 Replies
View Related
Feb 2, 2014

I need to solve about 450 excel sheets for my project.

The spreadsheet has X and Y data and curve is plotted. I need to split the curves into 3 parts based on slope change.

I can do it manually but I need an excel Macro which does this work.

A sample is attached : Excel forum-Lab data.xlsxâ€Ž

View 14 Replies
View Related
Dec 4, 2012

I have been doing some statistical anaylsis in excel and have been using trendlines within scatter charts quite a bit.

I know excel can display the equation of the line as well as the r-squared on the chart. Is there any way to get the values for the trendline to appear in a cell next to the corresponding chart data? Of course, I can simply enter the formula myself that is given on the chart, but I am wondering if there is a way to do it automatically.

I have included the data below along with the trendline value that I calculated by manually entering the formula from the scatter chart.

Code:

YXTrendline Value

$78.990.53$79.79

$78.730.53$79.73

$80.550.52$80.88

[Code]....

View 1 Replies
View Related
Apr 25, 2006

I need VBA to copy the values from a treadline output box on a chart to a new sheet. The macro recorder produces the code below, but I need to make the 5th line generic so that the code will apply to any workbook. The code below will not run without the "Windows("Test.xls").Activate" statement, which restricts the code to a workbook of a specifc name (Test.xls). How can I make this run for any workbook name?

Sub CopyTreadlineData()

Sheets("A").Select

ActiveSheet.ChartObjects("Chart 1").Activate

ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select

ActiveWindow.Visible = False

Windows("Test.xls").Activate

Sheets("Beta").Select

Range("B15").Select

ActiveSheet.Paste

End Sub

View 2 Replies
View Related
Nov 6, 2006

is it possible to export a trendline equation from a graph to a cell, or at least to export the coefficients? If yes, how?

View 4 Replies
View Related
Jun 7, 2008

I'm in the middle of designing a sheet that (amongst many other things) takes the equation of a graph trendline, pastes it, and calculates the various values along it. I can do it by hand, but when I try to record a macro, it doesn't register that I've highlighted the equation and hit copy. Here's the code I'm working with:

Sheets("Graphs").Select

ActiveSheet.ChartObjects("Chart 1").Activate

ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select

Selection.Copy

ActiveWindow.Visible = False

Windows("Copy of HRA Design calculator1.xls").Activate

Sheets("Binder @ max. stability").Select

Range("A1").Select

ActiveSheet.paste

The problem is line 4 - it can't copy because the text isn't selected.

View 3 Replies
View Related
Oct 3, 2006

i have a workbook that contains a series of worksheets. the workbook is a master document list. the first worksheet contains no data. the remaining worksheets are arranged so that A1 has the document number, A2 contains the document title, and A3 contains the review date. Id like to create a search macro that can search for the string entered into textbox1. if this is a number, the macro should search A1:A50 of all but the first worksheets in the workbook, and if its text, the macro should search B1:B50 of all but the first worksheets.

the macro should then select the cell containing what was searched. then id like the macro to prompt me and ask if id like to modify the reviewed date to todays date. d like to be prompted to continue searching for other records if they exist, and after that loop has finished to set the focus back to the search textbox.

View 2 Replies
View Related
Jul 17, 2014

i have a function in a cell (that works) to extract coefficients from a range of cells in a workbook:

VB: =INDEX(LINEST(CP25:CP27,CQ25:CQ27^{1,2}),1)}

i have variables for cp25:cp27 and cq25:cq27 already defined in my vba code. the values for these in the case i am working on are as follows (returns 110.5):

cp25 = 560

cp26 = 570

cp27 = 580

cq25 = .414

cq26 = .479

cq27 = .536

1) how to do this function in VBA only - this is part of a UDF and cannot have any helper cells

2) how to refer to 560,570,580 as a 'range'. is there a way to put these six variables into my ranges for later processing?

All of the google searches i have deal only with linear regression, taking from existing graphs, or say to just use the function i have above.

I have tried

VB:

Var = Application.WorksheetFunction.LinEst(Sheets("references").Range("CP25:CP27"), Sheets("references").Range("CQ25:CQ27^{1,2}"), 1)

[COLOR=#333333][/COLOR]

but return #value! errors. when i remove the ^{1,2} portion, i do return a value but it is incorrect (returns 160), what is the correct syntax for adding in the ^{1,2}? if you do that would be fantastic, but brings me back to issue #2 in that i need to refer to my variables in the vba code and not this range (as they will eventually be going away).

View 5 Replies
View Related
Apr 3, 2013

I have a workbook that I've built for a project. I've attached a sample workbook. What I'm trying to do, for the entire sheet or workbook if possible, is turn any Cell with a percentage of 30% to 49% yellow and any cell with a percentage of 50% or more Red. I would also like to move the ID's of the variable cells, for example Id number 9922, to the cells beside the description of the rows, Affected would be an example, if the information contained in the same row as the ID meets with a set of variables.

For example I only want the ID's moved if they correspond with IDsub 1-25. One more thing, the people who will be using this spreadsheet will be copying data from a website when it is imported it does not insert the values as numbers. I would like to format the cells, in example workbook they would be any of the cells labeled ID IDsub Variable or Number, so that anything put in that cell will automatically be converted to a number.

I should also probably add that the formatting will be done on approximately 80 rows a sheet with 47 sheets.

View 2 Replies
View Related
Apr 26, 2013

calculating the percentage of a percentage and writing the formula for excel.

There are 295 people in a room, of the 295, 75 or 25% are mothers. (I know how to calculate 25% - 75/295 = 25.42) of the 75 mothers 35 have 3 children, 32 have 2 children and 10 have 1 child.

35 is what percent of 25%

32 is what percent of 25%

10 is what percent of 25%

View 3 Replies
View Related
Oct 24, 2009

I have pv, fv, and period. there will be no payments made. I want the interest rate. I do it long hand it works. I do it using the rate function it doesn't! This also doesn't work with nper. Here is a simplified excel example. What am I doing wrong?

View 2 Replies
View Related
Mar 27, 2014

I have to pay 190000 $ in 180 months. I need a formula to establish an incresing rate for every month. I have a simulation on a paperwork, but I can't figure out the formula. On the paperwork the first rate in the first month is 875,21 $, the second one is 876.99 $, ..., and the last one in the last month is 1258,97 $.

View 5 Replies
View Related
Oct 15, 2008

I am trying to apply a multiplication factor that would vary depending on the sum of a particular cell. For example let's say the value of cell A5 is the SUM of A1:A4. Let's say the value is 255,000,000. my multiplication factor table looks like this:

1 - 50,000,000.........................(0.17)/1000

50,000,001 – 100,000,000..........(0.13)/1000

100,000,001 – 250,000,000.........(0.10)/1000

250,000,001 – 500,000,000.........(0.08)/1000

500,000,001 – 750,000,000.........(0.07)/1000

750,000,000 ++ .......................(0.05)/1000

So depending on the range the cell sum falls into I need the formula to apply the appropriate multiplication factor and divide by 1000. I'm trying to avoid manually inputting the (0.XX)/1000 based on the formula identifying which range the sum falls into and automatically applying the appropriate multiplication factor. In the example above the formula would be applied to the A6 cell and identify the A5 sum as falling into the 250,000,001 - 500,000,000 "range" and automatically apply the corresponding multiplication factor.

View 5 Replies
View Related
Aug 23, 2007

I would like to know the formula used in excel for rate function. Anybody knows and can share with me ? as i need to use that in other programmining language where these financial functions are not available.

View 9 Replies
View Related
Dec 27, 2009

A B C D 1 Date code price 2 10/09/08 abc101 1100.00 3 12/10/09 abc104 1255.00 4 30/08/09 abc101 1389.00

In D1 I want the last purchased rate of a code ( suppose here it is abc101, but it would be different for other item). Here Date is dd-mm-yy.

View 9 Replies
View Related
Aug 18, 2009

In Table A, I have the rate table for an insurance product. I need to build a formula that will outomatically populate the data in Table B based on plan, start age and deductibles.

Example, if a person selects age 32 years, deductibles as 0 and plan 4, then his yearly premium will be $750/12=$62.50.This will automatically increase when his age falls into the next age bracket which is 36-45 years and his premium at age 36 to 45 will be $970/12=$80.83.

The Table B should populate all this information from the start age to age 80 when the policy stops. An example of table B is shown in the attachement for someone who starts at age 32, chooses deductibles as 0 and plan 4.

View 2 Replies
View Related
Feb 4, 2009

I'm trying to calculate a sales run-rate which will change on a day-to-day basis, to predict the end-of-month sales total.

The invoice values are in the data range H17:H74 (I don't want the final formula to add up the refunds in this field i.e. negative values)

The date field is in data range C17:C74.

So basically the formula will need to add all invoice totals (excluding refunds) and divide by the current number of days worked in the month (not duplicating days in the date field) and then multiply this by the average number of working days in a month (21). This should should give a predicted end of month sales total.

Am I just making things up that are impossible to do on excel?

I've had a bet with the other guy in the sales office because I said excel can do a lot more than he thinks. He's under the impression that excel begins and ends with what they teach you at school.

View 12 Replies
View Related
Mar 6, 2007

Is there a function that calculates what interest rate is needed to turn a $2000 investment into $9000000 in 30 years based on compounding interest?

View 9 Replies
View Related