scatter plot problem, 100,000 data

Kay91

New Member
Joined
Jan 14, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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!


Knipsel1.png
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,120
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 14, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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.




Knipsel2.png
 

Kay91

New Member
Joined
Jan 14, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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

Knipsel3.PNG
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,120
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,832
Messages
5,627,152
Members
416,224
Latest member
RichardHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top