# scatter plot problem, 100,000 data

#### Kay91

##### New Member
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!

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### StephenCrump

##### MrExcel MVP
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
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

##### New Member
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

##### MrExcel MVP
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?

Replies
1
Views
268
Replies
1
Views
1K
Replies
4
Views
8K
Replies
0
Views
607

1,127,005
Messages
5,622,136
Members
415,879
Latest member
drumsmasher

### 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.

### Which adblocker are you using?

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

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