Hi there,
I am working on generating an excel spreadsheet that can undergo interpolation.
e.g. I have the following data
Sieve size (mm) Percentage passing (%)
500 "Blank Cell"
300 "Blank Cell"
150 100
100 "Blank Cell"
75 87
63 "Blank Cell"
53 68
37.5 51
26.5 41
19 36
16 "Blank Cell"
13.2 "Blank Cell"
9.5 "Blank Cell"
6.7 "Blank Cell"
4.75 3.5
2.36 3.3
0.075 2.9
The main aim of this excel spreadsheet is to find out what sieve size (mm) it is when the exactly 10% of the materials passes a particular sieve size.
So in the example above, given the data I received, it is logical to use the values 19, 36 and 4.75, 3.5.
So mathematically it would be:
x1 = 36% y1 = 19 mm
x2 = 10% y2 = x mm
x3 = 3.5% y3 = 4.75 mm
Therefore, 10% of the material will pass at 7.6 mm sieve size.
So far, I have a method/formula (=IF($D8>10,IF($D9<10,1,0),0))) that can interpolate ONLY when the blank cells have a value. Right now, the formula is only taking the values 19, 36 and 16, "Blank Cell" to interpolate which obviously gave me an incorrect answer.
I want to know if you guys can help me find a formula or a method that will automatically take the values 19, 36 and 4.75, 3.5 and ignore/skip the "Blank Cells".
Note: I need a method that is adaptable to all situations given the fact that the amount of data I receive varies, e.g. one day I could have 3 "Blank Cells" or 6 "Blank cells". The 17 sieve sizes used are fixed.
Or, if you guys have a different approach, I'd love to hear that as well.
I've spent an entire week trying to figure out how to solve this problem.
Your help will be much appreciated, thanks.
Bookmarks