View Single Post
  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jerry W. Lewis
 
Posts: n/a
Default Matching a value that appears multiple times

=LINEST(B1:B5,A1:A5^{1,2,3,4})
will return the coefficients of a 4th degree polynomial. Alternately,
Tushar Mehta has enhanced code by David Braden to extract coefficients
directly from a chart trendline
http://groups.google.com/group/micro...da30f29434786d
Note that for the chart trendline, you should format the equation to display
scientific notation with 14 decimal places.

In general, you should evaluate whether you can tolerate the wiggle of a
polynomial between the fitted points. It doesn't look too bad here.

Jerry

"BKGT" wrote:


Hi Martin,

Thanks for the suggestions. I was not aware that regression equations
could be extracted to use in formulas. Could you provide info on how
this can be done?

The goal of this spreadsheet is for me to just paste the data that I
receive from the instrument I am using and have excel instantly
calculate all the information that I am looking for without further
effort on my part :) . I have come up with an alternative way to
calculate what I'd like if I can somehow set a range based on the value
in another cell. I have been able to write the formula that picks the
x-value (column A) where the maximum y-value (column B) occurs. If I
could use that information to set the range for the MATCH function, I
could get around the problem I was having with it and just pick the two
values that are above and below the value I was looking for and
interpolate to get the exact number I want (I have done this in my
spreadsheet by manually setting the range based on my calculation for
the x-value where the y-value is max). Is this possible?


--
BKGT
------------------------------------------------------------------------
BKGT's Profile: http://www.excelforum.com/member.php...o&userid=33862
View this thread: http://www.excelforum.com/showthread...hreadid=536387