Finding points that fall below a curve

[istril]

I have a chart with about 15,000 points plotted. I added a logarithmic trendline, for which the equation is y=2.48*ln(x) + 7.11.

For each of these X,Y values, I want to identify which points are beneath the trendline. This is more a math question than an excel question, but it's been a while since my last math class... any help?

 

[dms37]

You just need an if function for each line. Assuming Y values in column A and X values in column B the following could be put in C and dragged down:

IF(A2<2.48*LN(B2)+7.11,"Below","")

 

[MickG]

My thoughts are if you put an "X" value in the formula based on your data you will get a value for "Y" if that value is greater than your "Y" value in your Basic data then that "Y" value in your data is below the line.
Or as "dims37" just said !!

 

[istril]

Yes, thank you both, basically the same strategy there, and it works.

One more thing; I just did it, and I'm finding that about 60% of my points fall below the trendline.

If I wanted to identify the "top" 50% of my points (or top 30%, 20%, etc) as compared to the trendline, is there a simple way to do this? What if I just fiddled around with my y value in the equation, or am I off base here?

I need a math refresher...

 

[istril]

ok, I just arbitrarily changed the Y value to "5", now I have about 41% of my points falling below the trendline. I suppose that's probably good enough, i can move the y value around until I get a "chunk" that is about the right size for me to work with.

 

[istril]

and for my above two posts, by "Y" value, I of course meant my "b" value. sorry.

 

[dms37]

To answer the top 20%, 30%..... above the trendline I suppose a simple method would be to do the Y value - the trendline value for the X value and sort by that difference:

=A2-2.48*LN(B2)-7.11

Alternatively if you want a percentage above the trendline (as valuse close to 0 may be less above the line but as a percentage of the expected be massively ove/under it perhaps try:

=(A2-2.48*LN(B2)-7.11)/(2.48*LN(B2)+7.11)

pros and cons to both depends what you need it for.