scatter plot problem, 100,000 data

[Kay91]

Hello all,

I'm new here and I hope somebody can help me.
I'm currently trying to finish my thesis and the chapter particle shape gives a bit of a problem.
I have 2 excel files with data that contains 32 columns of 1,048,576 rows of numbers. (each 2 columns are 1 data set)
So it's giving my poor laptop a really hard time.
I am now trying to make sense of 100,000 rows of data but my existing formula doesn't do the trick anymore to have a minimum and maximum.

The formula:
=IF(OR(L10<=325;L10>=1400);NB();L10)

Because increasing the data from 10,000 to 100,000 dots showed me something new.. Instead of removing everything before x=325 and after x=1400 there is a clear empty path between A and B (yellow line) and this line is not vertical unfortunately.

I tried my best but i can't figure it out how to remove all the dots of A/left of the yellow line.
The yellow line was found with trial and error.
I defined also a name "KAY" (excel 2016 -> formula -> name define -> =Blad1!$P:$P (is just a column with 1 till 100,000)
=IF((L4>=0.369*LN(KAY)-1.4124);NB();L4)
But this doesn't work.

(my excel is in dutch so i hope i translated everything correctly)

I hope somebody here can help me.
Goodnight!

 

[StephenCrump]

Welcome to the Forum!

Your question is well-translated, thanks, although in English versions, NB() --> NA().

I'm not clear why you are using fixed limits of 325 and 1400? If the aim is not to show any dots in region A, shouldn't you set to NB()/NA() if:

y (Circularity) > ln(x(Diameter))-1.4124?

 

[Kay91]

Thanks.
Ow yes of course NB() is NA()

Because with only 10,000 datapoints it looked pretty nice with those limits.
But with 100,000 datapoints there is still a part in A and i miss a part in B when using the limits.

 

[Kay91]

Welcome to the Forum!

Your question is well-translated, thanks, although in English versions, NB() --> NA().

I'm not clear why you are using fixed limits of 325 and 1400? If the aim is not to show any dots in region A, shouldn't you set to NB()/NA() if:

y (Circularity) > ln(x(Diameter))-1.4124?

Oke goodmorning again I think I was still partly sleeping.
=IF(M4>=(0.3707*LN(L4)-1.4124);NA();L4)

Works perfectly thanks!!!!!!!!!!!!!!!!!!!!!!!!

Do you have also a tip for finding the formula for the other 31 data sets? it looks nice but its for 99% perfect

 

[StephenCrump]

Do you have also a tip for finding the formula for the other 31 data sets? it looks nice but its for 99% perfect

How did you fit the first data set? What do you mean by:

The yellow line was found with trial and error.

If the question is: How do I use Excel to fit a logarithmic curve: y = m*ln(x) + b, you can use the LINEST function, i.e. LINEST( y values, ln(x values)), which solves for m and b using a least squares method.

But I am wondering:

- Are you breaking your data into 100,000 groups simply because of Excel's limitations? Is this statistically valid? Could you be using other software?
- Is it appropriate to fit a curve to this data set? Should it be a logarithmic curve? Should it be a least squares fit?

These are statistical questions for you (and perhaps your supervisor)- they are beyond the scope of an Excel Forum. I would expect that in writing your thesis, you would be allowed to ask for guidance/review from statisticians on campus about how you are analysing your data?