I am trying to find the exponential equation from a set of data in excel. The problem is that the graph does not follow the scatter plotted points on the graph. How would you remedy this?
View 1 RepliesI have a simple x y scatter graph, to which i have a trend line running through it, fine.
what i need to do is fine some values according to that trend line. e.g. my X axis goes up to 60 and my Y axis goes to 10, so if i draw accross at 55 from the trend line, i then go down to a number on my Y axis.
1. I use a number of x,y pairs
2. From x,y pairs I produce "scatter" type chart
3. From the chart I get trend line and its equation e.g. 5-order polinominal
4. I convert equation into excel formula
5. I apply formula onto the same range of x argument and produce graph
6. The graph is very much different from the trend line!!! I would expect exact match as I use equation/formula for the trend line.
On top of these graphs, i draw a line (insert a line shape) corresponding to a certain gradient in the data presented. I do not want this line to be exact (i.e. not point to point) as it is best to be drawn by eye so that the general 'gist' of the data can be found. What i would like, using any method available, is to be able to find the gradient of this line i have just drawn which obviosuly must be done taking into account the scale of the graph. The scales of the graph will not be consistent across all applications.
View 4 Replies View RelatedI've created a graph from data and need to add an average/net impact/trend line to the graph. From what I've read this needs to be an XY scatter, however, several efforts later and I can't seem to get it to work for me.
0514_Costs_DRAFT_ExcelF.xlsx
i have a stacked bar chart, similar to this: [URL] What i need is a trend line, similar to this: [URL] that shows the average of the bottom bar to date.
View 14 Replies View RelatedI want to show a stock chart in Open-High-Low-CLose format, then I hope that a trend line can be displayed at the same chart, what can I do? It seems like that stock chart can't be merged with another chart
View 7 Replies View RelatedIs there a way to get an equation for a trend line and have excel automatically export the equation to a cell where it can be used. The only way i currently know how to do this is to manually type the equation.
View 8 Replies View RelatedI currently have in my equation the trend function to linear extrapolate the date a line would cross either my upper or lower limits (100.1 and 19.9) between 2 data points. However if the 2 data points span both the upper and lower limits (as per the 2 attached examples) then unfortunately the trend function is only able to return the 1 date (where I've specified the x value). Is there any way for the TREND function to trend the 2 values? Do I simply add the 2nd x value to the new x's?
I have attached both the data and their graphs to visually explain my problem : EF Workbook.xlsx
I got a pivot table with following details Row Values: Dept_Code, Super_group, Product_Group Column Values: Ending_Date
Data: Sales, Availability%, Waste%, Number of Stores
i need to have a bar chart with trend line individually on each data items e.g.Sales, Availability% etc. when i tried to use trend line it prompts me to enter series i.e Ending_Date and it draws trend line for only that period comparing all data values. is there any way to have individual graph for Sales, Availability%, Waste%, Number of Stores with trend line covering
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 RelatedWhat I have is a column (column A) that only has two data entries at any one time.
I need column B to fill in the trend of those two data points.
My problem is I don't know how to deal with the cells that say #N/A. Which is every cell other than the two cells with data.
If I replace the #N/As with say 1, then it messes up the trend data points.
It seems like I need to be using the trend command, but any solution would be welcome.
Ok my data is like this:
data, data1, data2, "data99, data98, data97" ,data4, data 5 data, "data1a, data1b", data1, data2, data3 data, data, data, data,
and I use the following code to extract.
Code:
Open "D:data.csv" For Input As #1
do until eof(1)
Line Input #1, strData
myArray1 = Split(strData, ",")
For iCounter1 = LBound(myArray1) To UBound(myArray1)
Next iCounter1
Loop
Close
My problem is that it does not group the ones within the quotes, just splits them.
Two questions
1. Can my current code be modified?
2. I can use Input instead, to go one by one, and it recognises the quotes, but how can i tell I've moved to the next row of data?
I have created an AddIn for a project I'm working on using the 'Open' command in Excel Vb. While this worked perfectly for the file I was testing it on (1,740,754 bytes) it doesn't work on a new file (121,445,125 bytes).
On testing the code, one line at a time, it gets stuck on
Line Input #1, DataLine
The files only contain one line so I suspect there is too much data for the command to handle.
Is there an alternative?
I have the data to import / read in a pdf, in a doc, or in an Excel worksheet whichever is easier to use. I need to import the data, parse it into the correct cells for that row and then repeat the import until the end of the file. Not all the cells are in each group of data to import, so those cells will be null for that row. Some of the data for one cell may be in up to 14 lines in the data file. I have be concatenating these data rows into one cell. There are 48,000 lines in the file to import or I would do this manually. I am assuming that doing this in VBA would be the most efficient method.
View 11 Replies View RelatedI have about 180 workbooks which I need to compile into a Master Schedule.
All the tab 1's are different, these feed into tab 2, which the data has the same formatting throughout. The 2nd tab has the same data for A:F 1 but cells A:2 - F:2 down to row 9 are populated from tab 1, therefore different in each. I am trying to get a Master schedule that lifts the data in the fed cells into a line by line spreadsheet?
Is there a way I can get excel to look at a folder, then every workbook in it, the at the 2nd tab in every workbook, then list the cells as described above? I am not after a consolidation of this data, but a full list?
I need to read or clear all the line in specific range which will be increasing dynamically. Problem is "Selected File List" table range is not specified. New file chosen with browse will be added to "Selected File List" dynamically. Number of file can't be predicted.
vbaStk.JPG
What I've tried so far is, keep track of the browse button click and add the file path to arraylist. After that, (Row number 9 which is start line + arraylist size) to get the number of line of end of the table. But due to some requirement, I want to read the file from excel file. Something like -Read until found blank line or border bottom or something.
I'm new to VBA and I'm not so sure what I'm doing with vba codes
I am working with a program that creates numbers as follows:
Instead of -2.74E-6, the program writes -2.74-6. The E is left off to save space.
When I import this data, Excel automatically "does the math" so the cell reads: -8.74 instead of -2.74E-6.
I have the following data
A1 - 65.23
A2 - 1923.52
A3 - 945.18
A4 - 182.54
In column B
B1 =exp(A1)
B4 =exp(A4)
both are fine, however
B2 =exp(A2) - it is showing #NUM
B3 =exp(A3) - it is showing #NUM
Same case for all larger numbers
I have inherited a series of data relating to a change in a specification over a period of time and a number of cycles.
See attached.
There is already a chart which shows the data and has an exponential line.
I want to find the value of Cycles where the Average Flash exponential from the chart line is 0.131.
FYI this is to plot deterioration in a piece of tooling, 0.131 being the accepted warning level. If you feel there is a better statistical model to use for that application then I'm all ears!
I have a frequency table (attached) that I need to be able to interpolate values between the known values on something other than a linear basis (e.g. exponential or logarithmic).
View 5 Replies View Relatedinterpolating numbers in excel for an exponential function. I want to know how to be able to curve a curve in 4 different ways essentially the 2 that make up the two halves of a peak and the 2 that make up the two halves of a trough. I've tried using the LINEST function to create this interpolation and it's good to get 1 of the 4 curves I'm interested in. how to do the other 3? I'm also interested in knowing if these functions "whip the tail up" as in almost like how the tail end of a "sin" function flips back up. here it is.
[URL]
For this example of the exponential function, it creates the curve in one particular direction. I was wondering how I would do it to have it curve in another direction, for example I was testing the same function, but going downwards, and also having the tail curve back up (by hump I mean to simulate a gaussian distribution with the tails) how would I do that. seems like I now have part 1 of 4 aspects of the curve. here's the attached file to show what I mean. I want to get the hump going up and then sort of reflected along the y-axis and then similar features if I were to reflect the curves on the x axis. is it possible with the functions in excel? Linest test.xlsx
In excel if it changes number to exponential format it would default to, for example if number is 12345 -> 1.2345e4, 0.012345 ->1.2345e-2
I was wondering it is possible to which a function to change this default format ie for example 0.012345 -> 12.345e-3
I basically want this kind of formatting as its would display information in terms of milli, micro, pico (or the opposite side kilo, mega, giga and so on).
I have some short code that imports data and account numbers that is "|" delimited. The problem I am having is that during the import, excel converts some of the account numbers to exponential numbers.
e.g. it converts 542690E24 to 5.4269E+29.
Is there a way to avoid this from occuring during the import? I tried to format the destination cells as '000000000' (all accounts are 9 digits) but it didn't work.
to write a function that will exponentially weight a series of returns by a certain decay factor.
I have excel spreadsheets that can do this over several columns, like this
series of returns... rtns squared
2.50% =2.50%*2.50%
3.50% =3.50%*3.50%
5.00% =5.00%*5.00%
1.01% =1.01%*1.01%
0.75% =0.75%*0.75%
4.01% =4.01%*4.01%
-2.78% =-2.78%*-2.78%
I've been working on a ss that requires me to create a function to determine a value from a exponential curve where the area of the curve is variable . The terminal points, 0,0 and 100,100 remain constant and the curve is symmetrical at the 45degree angle. How can I derive a function knowing only these things.
Here is an image to crudely illustrate what i mean [url]
The area can be 10, 20, 35% it doesn't matter, I need a way to determine the function from just the area!
I have a bit of code that is grabbing only the first 9 positions of an alpha-numeric string. The problem I am running into is that when the alpha-numberic value contains an "E", the code turns it into an exponential number in error.
For example, I have the following in column G: 01877E107000. When I run the code, it turns into an alpha-numeric value. The result should be 01877E107 (in column K). I tried using the text function within the code.
I was wondering if anyone could tell me how to do away with the "Exponential" appearance of numbers (in Excel) when they are formatted as text. I am working with National Stock Numbers and there are no required computations based on them being formatted as a number ... AND ... one of my constraints is that they are required to be formatted as text when imported into an Access database ...
View 5 Replies View RelatedI use hex2dec frequently and input with a hex 'E' such as 05e8 is misinterpreted as exponential notation. I can use =hex2dec("05e8") to get the correct value, but when the hex number is in another cell, I get the exponent assumption/ conversion (to 5 x 10e8), or a # NUM error if it is in quotes. This seems like a blatant problem but I've found no mention of it in searches or FAQs.
View 3 Replies View RelatedI have two columns where TREND does not work well. I want to calculate the TREND value of Column 2 (PERCENTILE RANK) based on a new RAW number. If I TREND between just the two rows, the value is fine (i.e. If I TREND between 105 and 110 RAW, I get the right PERCENTILE RANK if the new X value is 106 (which is 15.3% PERCENTILE RANK). How can I make a formula that will only look at the two rows and TREND just those two rows to give me the right PERCENTILE RANK.
RAW PERCENTILE RANK
10514
11020.4
11121.8
11729.5
11833
12342.5
12444.5
12954.4...........