To find equation for two sets of non contiguous data

[Guest]

Hi
I have data like this:

Q C Q C Q c Formula (or equation)
a 1 10 2 20 3 30
b 4 40 5 50 6 60

I want a relation like y =m x or y= 3*e 0.2 ., or somethign liek that,
like how we get aan equation when we add a trend line. Is it possible to get
the equation for the above arranged data, and when i drag it to the next row
of data can the equation be updated using some trend formula or something...
IS this possible

 

[Jerry W. Lewis]

If you want function that relates Q and C, it would appear to be
C = 10*Q
or
Q = C/10

If you are asking whether those coefficients could be calculated by an
Excel function with more general data, then it depends on whether your
model can be expressed as a simple straight line using either the
original or transformed data; otherwise, no. Assuming that your data
are in A1:F2, you could array enter (Ctrl-Shift-Enter) the following
formulas
=SLOPE(IF(MOD(COLUMN(B1:G2),2)=0,B1:G2),IF(MOD(COLUMN(A1:F2),2)=1,A1:F2))
=INTERCEPT(IF(MOD(COLUMN(B1:G2),2)=0,B1:G2),IF(MOD(COLUMN(A1:F2),2)=1,A1:F2))

If your question is entirely different than the above, then you need to
be much more specific about what you are wanting to do.

Jerry

 

[Jon Peltier]

Jerry neglected to point out that this data arrangement is very
inefficient for the task at hand. A better arrangement would look like this:

a b
Q C Q C
1 10 4 40
2 20 5 50
3 30 6 60

This way each X range and Y range pairing are in adjacent columns, much
easier to write formulas and select ranges.

So many difficulties in Excel are eliminated by using a proper data layout.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______