Get y= eqn / fx from coords efficiently

[MattLogue9]

Making an AQMS. Air Quality Monitoring System.

I want to be able to determine specific gas that elevated sensors readings. Using MQ gas sensors and an esp32 MCU. the existing libraries are not sufficient.

Now each sensor is sensitive to a few and not others and there's overlap... It's like ABD, ACE, BCD, BDE. So if I have those four sensors and #1, #2 go up, I know it's gas A. If 12 & 4 it's B, so on.

Anyhow, what I need help with is getting eqns made for the sensitivity curves (see example attached). A ratio changes based in gas concn. I want to simply input 3 coordinats and have VBA &Excel find best eqn and poop it out onto sheet. It would make eqn for every sensor*gas.

.. I figured out how to get eqn on chart but not sheet...

[MrShorty]

Since this looks like an extension of the previous thread (https://www.excelforum.com/excel-cha...-and-more.html ) I still recommend using LINEST() for the regressions. Where most of your curves are linear (or mostly linear) on a log-log plot, I would probably use a power trendline rather than a polynomial as suggested in the previous thread. power trendlines (y=ax^b) are linear on a log-log plot as can be shown by taking the log of both sides ln(y)=ln(a)+b*ln(x) (substitute u=ln(x) and v=ln(y) to get v=ln(a)+bu if needed to see the straight line relationship). Something like =LINEST(LN(known_ys),LN(known_xs)) will return the parameters for a power trendline.

You may find this tutorial useful, as it describes the different trendlines available to charts as well as equivalent worksheet formulas for each trendline type. https://www.ablebits.com/office-addi...ower-trendline

[MattLogue9]

Yeah i forgot... so i think i may of misconceptialized this... so I still use a straight line eqn y=mx+b, but the axis's are log! What offset are m and b from the returned array of linest()? (dumb ? i know)....

[MrShorty]

I'm not sure I understand your question. The LINEST() output is m(n), m(n-1),...,m(1),b. If you include the additional statistics (4th argument is TRUE), the LINEST() will output additional statistics on successive rows (see the help file for the exact output). So, for a basic power trendline [y=b*x^m equivalent to ln(y)=ln(b)+m*ln(x)], LINEST(LN(known_ys),LN(known_xs)) will output m in the first cell and ln(b) in the second cell.

Does that answer your question, or am I misunderstanding your question?