# 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
ADVERTISEMENT
Nov 24, 2009

I have a list of 500 values calculating when a plane will stop on a carrier deck, i have IF formulas for each row saying whether the plane has stopped or is slowing. At some point the plane stops then this column reads "stopped". However i would like to see when this point is without having to scroll down 400 rows to find where this column changes, basically i want this:

=IF(L8="Stopped",D8,IF(L9="Stopped",D9,IF(..... 500 times over, however this wont fit in the box!.

What i need is a cell that says when the force stopping the plane is greater/equal to the total energy of the plane, the plane has stopped.

View 4 Replies
View Related
Jun 5, 2003

How to do the following, which solves the equation, in Excel.

LH

W1

W2

(LH-X)/(LH+X)=W1/W2

(6-x)/(6+x)=.6/1.25

6*1.25-x*1.25=6*.6+x*.6

7.5-1.25x=3.6+.6x

7.5-3.6=1.85x

3.9/1.85=x

x will equal 2.108..... in this case

View 9 Replies
View Related
Dec 31, 2013

I had a question about adding numbers, sort of like algebra equations (best description I can think of) into a spread sheet. Example:

Cell A1: 8

Cell A2: 8l

Cell A3: 8ls

Cell A4: 8l

Cell A5: 8

Cell A6: -where I want the total to be displayed in the following way: 16+16l+8l

Is this even possible to calculate by grouping like numbers?

View 2 Replies
View Related
Feb 12, 2014

Index of the VBProj.VBComponents("ThisWorkbook") component?

I need to refer to that component but not by its name because the file I use, goes to some users that have MS Excel installed with another language and their Excel translate the component's name in that language so

VBProj.VBComponents("ThisWorkbook") should be VBProj.VBComponents("[that-language-translation]")

View 3 Replies
View Related
Aug 11, 2008

I have been sent a spreadsheet containing the following Macro, which is fair enough, only thing is, I don't understand the Macro, its too high for my extremely basic knowledge of VBA.

Private Sub CommandButton1_Click()

Dim rng As Range

Dim i As Integer

Dim sht1 As New Worksheet

Dim sht2 As New Worksheet

Set sht1 = Sheets(ActiveSheet.Range("D2").Value)

Set sht2 = Sheets(ActiveSheet.Range("D3").Value)

Set rng = sht1.Range("First_Line")

i = 0

Do Until sht1.Cells(rng.Row + i, 1) = ""

sht2.Range("Managers_Name") = sht1.Cells(rng.Row + i, 1)

sht2.PrintOut

i = i + 1

Loop

End Sub

translate this for me, step by step, as to what this Code is doing?

View 2 Replies
View Related
Feb 29, 2012

I have a spreadsheet with thousands of rows of text that I need converted from one language to several other languages.

Is there a macro I can use that taps into some free service, running down my page and giving a foreign language conversion for each cell?

[URL]

View 14 Replies
View Related
Dec 14, 2006

How would I go about putting the following formula into VBA code? The .008 would be going in field O8. =IF(AND(M8>5,M8<10,N8>5,N8<10), 0.008)

View 5 Replies
View Related
Feb 9, 2007

It looks like the above has been inserted into my current workbook (by mistake). How do I delete it all (lots of the options have the remove option disabled)

View 3 Replies
View Related
May 8, 2007

I am trying to find a way to make a certain number equal a certain name. For example 1111 will equal Name1 in another cell and 1112 will equal Name2....

View 5 Replies
View Related
Apr 23, 2014

I have excel workwook with Month sheet Jan,Feb,Mar....Dec and YTD

In each monthly sheet i will have either "yes"or "No" in cell B66:K75.

Below is what i need to do in YTD sheet cell B66:K75. Each cell in TYD B66:K75 will have 12 digit number

Need to go thru each monthly sheet for each of the above cell and find if Yes or No and translate that into number (yes = 1, no = 0)

Example:

If B66 in Jan, Feb and Mar is Yes and B66 in Apr thru Dec is No then the number in YTD in cell B66=111000000000

View 5 Replies
View Related
Apr 9, 2009

I have 3 columns of data, column C has a number representing a length of time in months. I need to add a number to the end of each row (Column D), depending on the number in column C. Example:

If column C has a number between 0 and 3, the number to go in D would be 1. If column C has a number between 4 and 6, the number to go in D would be 2. Here are the list of possiblities (only 5):

0-3 = 1

4-6 = 2

7-9 = 3

10-12 = 4

12+ = 5

This will form part of a macro, so a VBA solution would be ideal. I thought of having a named range and looking up each row with a list of all possiblities. But i see that as rather crude. I'm hoping someone has a more logical solution they could share.

View 2 Replies
View Related
Nov 23, 2009

I am trying to create a formula that can translate true calendar dates when a date is entered IE 2/15/09 if with the normal formaula, it alwways calulates 30 days or even on 31 day months.

I need th formula to calculate actual dates and know when there is a 31 versus a 30 day month or 28/29 for February.

View 9 Replies
View Related
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
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
Dec 9, 2009

I've developed a spreadsheet in Excel 2000 where one of the cells contains the number of months a quantity will last, calculated by taking the total amount and dividing it by the amount used each month (a constant).

I would like to have another cell where that number is translated into the future date that number of months represents, so that in addition to seeing how many months the total amount will last, one can see when the amount will be depleted.

For example, suppose it is December 9, 2009 (as it is now) and the number in the number-of-months cell is 6. Then, is there a way that when 6 is calculated, in another cell appears something like June 9 2010 or 6/9/2010 or 6/9/10 (assuming the computer's clock is correct)?

It would be nice if the formula would handle decimals too, such as the date for 6.4 months, but that's not essential.

View 9 Replies
View Related
Mar 25, 2013

Lets suppose that in a drop down box I have the words : "I" "want" "to" "create" "a" " sentence".

Well what I would like to do is have each word as selected in the drop down box, placed in another cell creating a sentence.

If you picked the words in order the cell should read: I want to create a sentence or it could read any combination of these words depending in the order they were picked. So it could read: I sentence want to create a sentence if the words were selected in this order.

View 14 Replies
View Related
Mar 17, 2013

How do I translate this to a vba code?

=INDEX('Workers List'!D3:D13,MATCH('Workers List'!C4,'Workers List'!C3:C13,0),1)

what i have done is

test = Application.Index(Sheets("Workers List").Range("C3;C13"), Application.Match(1, Sheets("Workers List").Range("D3:D13"), 0), 1)

But i keep getting an error, may i know whats wrong with it?

View 5 Replies
View Related
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
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
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
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
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
Dec 21, 2008

I have some data for international dialed numbers, I need to translate this into a country or destination name.

for example if the dialled number begins with 001, the is the united states etc..

I have all of the required codes, but they all differ in length.

ie 0033 is spain but 003356 is a spain mobile.

I was thinking that i could use VLOOKUP to find the relevant text to display, but this does not work with the varying lengths of the codes.

View 9 Replies
View Related
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
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
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
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
View Related
Apr 19, 2014

In Excel 2013, how do I translate all characters of selected cells to Unicode?

There is a formula to translate the first character into Unicode [=UNICODE(text)] and that formula would have worked fine for me .Only if it could translate all the characters to Unicode not just the first one.

View 1 Replies
View Related
Apr 19, 2014

In Excel 2013, how do I translate all characters of selected cells to Unicode? There is a formula to translate the first character into Unicode [=UNICODE(text)] and that formula would have worked fine for me .Only if it could translate all the characters to Unicode not just the first one.

View 10 Replies
View Related