Non Linear Scale Applied To Data

Mar 8, 2008

in this example below i need to work out what the divsor would be for values inbetween the data?

View 9 Replies


ADVERTISEMENT

Calculate A Linear Sliding Scale

Oct 9, 2008

I need a formula to calculate a linear sliding scale from 10%-30% (no higher, no lower). Based on the data in the below chart, if the GROR (let's say this is known and the result resides in cell A1) is less than or equal to 0.0% the payout % is 10.0% and gradually increases as the GROR increases.

The payout % is capped at 30% which is if GROR is 18% or greater. Below are just some examples of how certain thresholds should calculate.

GROR 0.0%5.0% 8.0% 11.0% 14.0% 18.0%+
payout %10.0%10.0%14.6%19.2%23.8%30.0%

View 2 Replies View Related

Linear Regression Over The First 5 Points In The Set Of Data On The Same Graph

Mar 2, 2009

I have a set of data. I know how to do linear regression over the whole set of data. How do I have another linear regression over the first 5 points in the set of data on the same graph ?? I am using Excel 2007

View 14 Replies View Related

Convert Time On Scale Of 100 To Scale Of 60?

Feb 1, 2013

How can we convert time on a scale of 100 to a scale of 60?

View 3 Replies View Related

Forecasting Using Historical Seasonally Effected Non-linear Data

Jan 15, 2010

Forecasting using historical seasonally effected data

I organize my thoughts in numbers so it will be easier to understand.

[1] Say I have this data from January 2009 to December 2009:

1-4-7-10-13-14-15-16-18-20-22-25

As you can see, the growth is not linear.

[2] So then, I start with this data point for Janaury 2010.

26-a-b-c-d-f-g-h-i-j-k-l

[3] I now want to forecast that data point for the rest of 2009...using the growth in [1]. SO i guess that's an easy task right? Take the percentage change from Jan 08 and Feb 08...and apply that to the 26...and so on. (am i correct here at least).

[4] But lets say we have a GOAL. So, this become a forecast/plan. In December 2010, i want to grow double the amount of that in December 09. So 25 x 2 = 50. Now, the "known" data is like this:

26-a-b-c-d-f-g-h-i-j-k-50

So how do i do this now?

View 9 Replies View Related

Sum Filtered Data After Filter Is Applied

Jan 20, 2009

I have a spreadsheet that I want to sum the data similar to SUMIF or SUBTOTAL after a filter is applied. So I have data in column "F" that I appliy a filter and based on that filter I want to sum only the filtered totals in column H. Is there an easy way or is creating a new function the only way to go?

View 9 Replies View Related

Rank Data With Subtotals Applied

Sep 23, 2009

I have a worksheet that I'm trying to rank program selling. The range has subtotals that I need to exclude, so I'm trying to rank over multiple ranges. I've thought about LARGE in an array function, but can't seem to make that work. Is SUMPRODUCT a possibility? I've attached the worksheet, trying to get the top programs (again excluding the subtotals in bold).

View 4 Replies View Related

Number Format Not Applied To Pasted Data?

Jan 5, 2014

I have a thoroughly outdated system to keep a log of call centre stats. In order to facilitate a marginally easier workflow, I have rearranged a few things so that a user simply has to copy and paste data from reports into the worksheet.

The worksheet is set up with all the number formats like time and percentage which are extremely important in totaling and averaging hours and availabilities, however the pasted data doesn't match the destination format and it therefore affects the total.

I have tried many different special pasting options to no avail.

The only way i seem to be able to do it is to go into each line and retype my percentages (as 100 becomes 10000% ) and click the tick next to the formula bar so 00:00:16 is calculated as a quantity of time instead of text. With 30 people and 15 stats each day that exercise becomes a little bit tedious and counterproductive.

View 9 Replies View Related

Z-Transformation (scale My Data)

Oct 14, 2008

My question is that i have to scale my data.. Can i do Z-transformation..
Is there any inbuilt function or shall i have to write Code,

View 9 Replies View Related

Time Related Data Applied Over Time Period

Jul 10, 2014

I want to return a value based on once off time related data which is captured at 4 time periods throughout a day. I have another set of data which does not line up with this data but I want it to return the value closest earliest value.

Given this data:
27-6-14 3:00 12
27-6-14 7:00 18
27-6-14 11:00 19

If I have a time like 27-6-14 5:00 I want to return a value of 12 (Previous time). How would this be possible?

View 5 Replies View Related

Linear Regression?

Jul 21, 2006

I was handed the attached file. understand everything except how the values in row 6 were derived No formula present when I received the file, just the numbers. Row 7 is hard entered scores the units achieved

View 8 Replies View Related

Linear Interpolation

Feb 10, 2007

need an excel simple formula or function that returns values of y for given x in a two-dimensional array and works in the following way.actually a combination of linear interpolation and lookup/linear search. as in following example

x y
1 10
2 15
3 20

if x=1.5 then y=12.5 (answer available thru FORECAST but incomplete)
if x=2 then y=15 (answer available thru LOOKUP but incomplete)

so for whatever the x i need/get a valid y thru a single formula/method !
sorry m not good in math,

View 9 Replies View Related

Multiple Linear Regression In VBA?

Oct 16, 2008

I would like to run a multiple linear regression in vba. I have one dependent and three explanatory variables. I will have to use a macro of some kind, since I need to run too many regressions to do it manually. To simplify things a little bit:

- There will always be exactly three independent variables

- There are no missing values

- The data is allways numerical

I've already got four ranges defined: Yrange, X1range, X2range, X3range. I would like to take these ranges as input parameters for the regression model. The only two parameters I need are Sum Square for Regression (SSR) and the degrees of freedom. I understand that you can use excel's matrix formulas to calculate some of the input parameters, but one doesn't really get around vba. Any (simple) source code allowing me to conduct a regression with three input parameters?

View 7 Replies View Related

Absolute Value To Linear Program

Mar 29, 2013

I have to convert the first to the second program, but i don't know how to do this:

MAD.png

ABStolinear.xlsx: the first sheet is the first program which is correct (i think), the second sheet is a failed attempt to write the second program

View 5 Replies View Related

Bi-linear Interpolation VBA Code

Feb 10, 2009

I have 3 equations and have created the relevant chart from them. What I would like to do is put in a value of x & y and get result z. Having done a search through previous threads I found some BiLinear Interpolation code that works using the table in a workbook, but what I'd really like is the Interpolation of the formulae rather than the resulting table and for it to be embedded in VBA.

View 6 Replies View Related

Forecast Linear Trend Between Min & Max

Jan 4, 2007

I am using the Forecast formula to give me a value from a Linear trend. However I need to limit the resulting value that is displayed within a min and max value. Background: This is to allow me to calculate the amount of bonus someone will earn depending on the Percentages of their target they achieve. The min bonus is 25% when 80% of target is achieved and max bonus is 100% when 125% or greater of target is achieved.
So the Forecast formular works great using the existing values, but i need help limiting the results to between 25% and 100%. I was trying to use a sumif formula to copy the resulting cell but only if the value fell between the 25 and 100%. I've attached the spreadsheet as I think it will highlight what i'm trying to do better than I can explain it.

View 2 Replies View Related

Calculate Linear Interpolation

Oct 4, 2007

In the attached sheet, I have measured data from a device. It shows Input, Output, gain and delta. You can see as input increases, delta comes down. I want to find the output corresponding to delta od -1. In the table, I have enteries for delta of -0.982 then -1.382, not for -1. How to find output corresponding to delta of -1?

View 6 Replies View Related

Linear Regression On Single Set Of Numbers

Jan 27, 2013

If I have a set of numbers 1,2,3,4,5,6,7,8,9 in cells, how can I get a line of best fit, linear regression, in the cells next to them?

View 1 Replies View Related

Linear Weighted Moving Average

Nov 25, 2006

I have done quite a bit of looking on Google and looked over the posts in this forum, however, I can't find an example Excel worksheet for a linear weighted moving average.

The data set I am applying this to has 180 data points and the linear weights should extend back over the last 30 points.

View 9 Replies View Related

3D Linear Algebra. Translate To Best Fit Plane

Mar 20, 2008

I have several hundred points in cartesian x,y,z coordinates. I used an Excel add-in program to calculate the equation of best fit to the points. The equation is:

z=A*x+B*y+C
where (for this case):
A= 2.29091
B= -1.77338
C= -192.494

My objective is to define the several hundred x,y,z points in cartesian coordinates about a new cartesian coordinate system whose origin is the centroid of the data points and whose z plane (ie z=0) is equal to the best fit plane defined above (when referenced from the original coordinate system).

Using Pro/e (a cad program), I was able to create the new coordinate system using the x, y, and z intercepts from the best fit plane, and then was able to redefine the points about the new coordinate system. After the points were redefined in Pro/e, I was able to export the points back to Excel; however, I would prefer to only use Excel / VBA because I prefer the calculations be transparent for optimal accuracy and validation.

View 9 Replies View Related

{Sum(if(... )} Applied To Whole Columns

Jun 23, 2008

I cannot acheive to have a {Sum(if(...)} formula applied to whole columns. See below.

The following formula return a numeric value. So it works.
{=SUM(IF(Query!$L1:$L65535=TEXT($A37,"@"),Query!$AM1:$AM65535,))}
(confirmed with Ctrl+Shift+Enter)

The following formula returns #NUM. So it fails.
{=SUM(IF(Query!L:L=TEXT($A37,"@"),Query!AM:AM,))}
(confirmed with Ctrl+Shift+Enter)

how to use {Sum(if(... )} to whole columns?

View 9 Replies View Related

Linear Regression Array - Significance Of R Testing

May 5, 2014

I have set up a linear regression array in Excel and now want to test the significance of my r2 value at a certain level of significance. I've only been able to find tables that give the critical r value, but I want to test it at 99.73% level of confidence and none of them contain that specific value. Is there a way to do this in Excel?

View 3 Replies View Related

Macro To Run SOLVER Across Several Sheets On Linear Program

Nov 18, 2012

Writing a macro to run SOLVER on a linear program. I have twenty tabs, each with a linear program and a solver already set up (constraints, cells to change, and cell to maximize). I would like to run a sub that will solve each tab's LP by running the tab's SOLVER...

If it makes it easier, my cell to maximize is I17, by changing B16:H16, subject to I20:I40 SolverAdd -> SolverOk -> SolverSolve

View 1 Replies View Related

Fill Blanks With Linear Numeric Series

Apr 18, 2008

I would like to find the blanks within a column. Using the available cell values above and below compute a sort of linear function then, using that linear function fill in the blanks in between. So the values are linearly increasing or decreasing (whatever the case maybe) to the next available nonblank cell value. spreadsheet attached.

View 3 Replies View Related

Intersecting Points Of Chart Linear Plots

May 10, 2008

I would like to find the intersection points of all of the linesshown on the included chart. If that could be output into a table, by the day, that would be ultimately what I am after. http://bladeseri.50megs.com/Intersects.png. Guess 50megs.com doesn't allow this type of reference. Here is Geocities link http://www.geocities.com/bladex2007/Intersects.png

View 5 Replies View Related

No More New Fonts May Be Applied To This Worksheet

May 10, 2007

I have a very simple workbook in Excel 2000 with one page containing all my data which I update on a daily basis. Some of the data is linked to another workbook so that it updates each time I open it. Then I have 12 tabs, one worksheet for each month of the year with 10 simple graphs on each worksheet.

As you can see, it is a very simple worksheet. Each month, I copy and paste the graphs from the previous month into the current month and simply alter the Source Data in the graph to gather the current month's data. At the start of this month, it allowed me to copy and paste, but when I tried to alter the Source Data to the current month, I got a window on screen with a yellow triangle containing an exclamation mark and the words "No new fonts may be applied to this worksheet". If I click the OK button or the cross, it responds but the whole programme is locked. The only way I can get out is to give the 3 fingered salute and close Excel down.

View 2 Replies View Related

No More New Fonts May Be Applied In This Workbook

Oct 10, 2007

I have a workbook with 33 worksheets and more than 150 charts. Now, when I right-click on a chart to access "Source Data" for example, I get a message saying "No more new fonts may be applied in this workbook". Then I have to click "OK" about a dozen times before I get access to the dialog box. Curiously, it happens for some charts and not for some others.

View 7 Replies View Related

Excel 2010 :: Finding Values Using Linear Extrapolation?

Mar 26, 2014

I am looking to find out certain values from a current set of data using linear extrapolation in Excel 2010.

I have attached the data i am using below:

Data.png

I need to find out the specific power output and heart rate values, at a set blood lactate value (i.e. When the blood lactate value is exactly 2.0mmol.L-1, what is the given power output and heart rate).

I have tried plotting the data into a line graph with a secondary axis, however because i am unable to change the horizontal axis range for the power output values, i cannot interpret the data in this way.

View 14 Replies View Related

Estimate Linear Formula From Multiple Coordinate Pairs

Aug 13, 2007

I have 32 pairs of coordinates (X,Y). If I graph them using a scatter chart in Excel, I can see that it looks like they suggest a linear relationship between the points.

Other than printing the chart and trying to draw a "best fit" line manually, is there some sort of way I can get Excel to come up with the "best" linear formula (y=mx+b format)?

View 9 Replies View Related

Automating Linear And Growth Trend Auto Fill

Jun 16, 2008

I used HTML Maker to show my sheet below. I want to have cells K3:Z3 automatically populated using the same calculations as "Linear Trend" Auto Fill and K4:Z4 using "Growth Trend" Auto Fill without having to do it manually. The data in Row 2 will of course be continually updated with actual scores so the remaining empty month scores would need to be updated again. I am using this to come up with a more accurate prediction method than a simple trend line in Excel graphs.

******** ******************** ************************************************************************>Microsoft Excel - Auto Fill setup for questions.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutL14=ABCDEFGHIJKLMNOPQRSTUVWXYZ1Months00.030.10.250.512345678910111215182124273033362Score10 1010 9887                3Linear Predicted10 1010 9887                4Growth Predicted10 1010 9887                5Auto filled Linear in Red10 1010 98877.066.666.275.885.485.094.74.33.913.513.122.732.331.941.551.156Auto filled Growth in blue10 1010 98877.146.826.526.235.955.695.435.194.964.744.534.334.143.953.783.617Actual scores10 1010 98877554456         Sheet1 [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.

View 26 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved