Fitting different lengthened intervals together

A

alanlambden

Board Regular
Joined
Nov 20, 2014
Messages
70
Hi,

I am hoping to get some help with some excel arithmetic. This is quite a difficult issue to explain. My VBA coding is fairly rudimentary too so I was hoping to crack this problem in the cells.

I'm working with drillhole data. I have two sets of data that I need to calculate some ratios based on a column in each dataset and converge into one column.

The first is the sampling intervals. Shown here on the left. These are the interval that are sent to the lab for analysis. We happen to be analyzing rock for gold. Below you can see that from between 0-24.1m we had 12 samples. Most of them were around 2m wide, some are slightly larger and some are smaller.
The second set of data is what we call in the industry as 'recovery'. This is when we estimate how much of the rock was intact after being extracted from the ground. For example if we pull up a 1m sample and the rock is solid with no breaks, thats 100%. If the drilling intercepts a fault or a highly weathered piece, it is likely that the rock will be crumbly and less-than-intact. We use a technique to measure how much rock is there against how much should be there and assign it a percentage. So after measuring the interval and there is only 75cm of intact rock and the rest is mud/sand or whatever, that would get assigned a 75% recovery.

You can see below that there were many more recovery intervals taken than sample intervals. What I would like to do is assign each sample interval a recovery percentage, calculated from the actual recovery data. That would mean some arithmetic to adjust the ratios of each recovery interval to fit into the sample interval.

So the first sample interval 0-0.4 would be assigned 100% because the first interval in the recovery(%) data envelops this interval completely and exactly. The 0.3m that stretches into the next sample interval, must then be weighted (.3m x 100%) and added to the next recovery interval (1.2m x 45.8% - the 1.2m being the total length of the interval 1.9m - .7m). The sampling interval 0.4m - 3.4m must be satisfied with all the remaining recoveries. So then the code must also add (0.6m (because 2.5m-1.9m) X 66.6% ) and (0.5m (because 3m-2.5m) x 100%) and then the final recovery falls nicely on 3.4m so we must add (0.4m x 100%) adding all these weighted recoverys up and then divide by the sample length will give us the amalgamated recovery for the interval.....and then proceed to the next sample interval and fitting the recoveries in accordingly. Here is the arithmetic for the second interval...but I am having a lot of trouble getting excel to do this for me.

.3m x 100% = 30
1.2m x 45.8% = 55
0.6m X 66.6% = 40
0.5m x 100% = 50
0.4m x 100% = 40

30 + 55 + 40 + 50 + 40 = 215/3m = 71.7%. I want to populate the remaining cells in the 'adjusted recovery' column.

Sorry if I made it sound really complicated!



Code: 
[TABLE="width: 747"]
<tbody>[TR]
[TD="colspan: 3"]Sampling intervals[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Recovery[/TD]
[/TR]
[TR]
[TD]Hole #[/TD]
[TD]From[/TD]
[TD]To[/TD]
[TD]length[/TD]
[TD="colspan: 2"]Adjusted recovery[/TD]
[TD]From[/TD]
[TD]To[/TD]
[TD]length[/TD]
[TD]Recovery (%)[/TD]
[/TR]
[TR]
[TD]DH-001[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.7[/TD]
[TD="align: right"]0.7[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]DH-001[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]3.4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]71.7[/TD]
[TD][/TD]
[TD="align: right"]0.7[/TD]
[TD="align: right"]1.9[/TD]
[TD="align: right"]1.2[/TD]
[TD="align: right"]45.8[/TD]
[/TR]
[TR]
[TD]DH-001[/TD]
[TD="align: right"]3.4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2.6[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1.9[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]0.6[/TD]
[TD="align: right"]66.7[/TD]
[/TR]
[TR]
[TD]DH-001[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8.1[/TD]
[TD="align: right"]2.1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]100.0[/TD]
[/TR]
[TR]
[TD]DH-001[/TD]
[TD="align: right"]8.1[/TD]
[TD="align: right"]10.1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3.4[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]100.0[/TD]
[/TR]
[TR]
[TD]DH-001[/TD]
[TD="align: right"]10.1[/TD]
[TD="align: right"]12.1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3.4[/TD]
[TD="align: right"]4.05[/TD]
[TD="align: right"]0.65[/TD]
[TD="align: right"]96.9[/TD]
[/TR]
[TR]
[TD]DH-001[/TD]
[TD="align: right"]12.1[/TD]
[TD="align: right"]14.1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4.05[/TD]
[TD="align: right"]4.7[/TD]
[TD="align: right"]0.65[/TD]
[TD="align: right"]53.8[/TD]
[/TR]
[TR]
[TD]DH-001[/TD]
[TD="align: right"]14.1[/TD]
[TD="align: right"]16.1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4.7[/TD]
[TD="align: right"]5.55[/TD]
[TD="align: right"]0.85[/TD]
[TD="align: right"]82.4[/TD]
[/TR]
[TR]
[TD]DH-001[/TD]
[TD="align: right"]16.1[/TD]
[TD="align: right"]18.1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5.55[/TD]
[TD="align: right"]6.4[/TD]
[TD="align: right"]0.85[/TD]
[TD="align: right"]94.1[/TD]
[/TR]
[TR]
[TD]DH-001[/TD]
[TD="align: right"]18.1[/TD]
[TD="align: right"]20.1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6.4[/TD]
[TD="align: right"]7.25[/TD]
[TD="align: right"]0.85[/TD]
[TD="align: right"]94.1[/TD]
[/TR]
[TR]
[TD]DH-001[/TD]
[TD="align: right"]20.1[/TD]
[TD="align: right"]22.1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7.25[/TD]
[TD="align: right"]8.05[/TD]
[TD="align: right"]0.8[/TD]
[TD="align: right"]100.0[/TD]
[/TR]
[TR]
[TD]DH-001[/TD]
[TD="align: right"]22.1[/TD]
[TD="align: right"]24.1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8.05[/TD]
[TD="align: right"]9.2[/TD]
[TD="align: right"]1.15[/TD]
[TD="align: right"]100.0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9.2[/TD]
[TD="align: right"]10.3[/TD]
[TD="align: right"]1.1[/TD]
[TD="align: right"]90.9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10.3[/TD]
[TD="align: right"]11.3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100.0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11.3[/TD]
[TD="align: right"]12.8[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]96.7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12.8[/TD]
[TD="align: right"]14.3[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]100.0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14.3[/TD]
[TD="align: right"]15.8[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]100.0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15.8[/TD]
[TD="align: right"]17.3[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]100.0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]17.3[/TD]
[TD="align: right"]18.8[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]100.0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18.8[/TD]
[TD="align: right"]19.9[/TD]
[TD="align: right"]1.1[/TD]
[TD="align: right"]100.0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]19.9[/TD]
[TD="align: right"]20.6[/TD]
[TD="align: right"]0.7[/TD]
[TD="align: right"]100.0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20.6[/TD]
[TD="align: right"]21.4[/TD]
[TD="align: right"]0.8[/TD]
[TD="align: right"]100.0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21.4[/TD]
[TD="align: right"]22.9[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]100.0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]22.9[/TD]
[TD="align: right"]24.1[/TD]
[TD="align: right"]1.2[/TD]
[TD="align: right"]100.0[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Maybe this...


A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
1​
Sampling intervals​
Recovery​
2​
Hole #​
From​
To​
length​
Adjusted recovery​
From​
To​
length​
Recovery (%)​
3​
DH-001​
0​
0,4​
0,4​
100,0​
0​
0,7​
0,7​
100,0​
4​
DH-001​
0,4​
3,4​
3​
71,7​
0,7​
1,9​
1,2​
45,8​
5​
DH-001​
3,4​
6​
2,6​
80,9​
1,9​
2,5​
0,6​
66,7​
6​
DH-001​
6​
8,1​
2,1​
96,5​
2,5​
3​
0,5​
100,0​
7​
DH-001​
8,1​
10,1​
2​
95,9​
3​
3,4​
0,4​
100,0​
8​
DH-001​
10,1​
12,1​
2​
97,8​
3,4​
4,05​
0,65​
96,9​
9​
DH-001​
12,1​
14,1​
2​
98,8​
4,05​
4,7​
0,65​
53,8​
10​
DH-001​
14,1​
16,1​
2​
100,0​
4,7​
5,55​
0,85​
82,4​
11​
DH-001​
16,1​
18,1​
2​
100,0​
5,55​
6,4​
0,85​
94,1​
12​
DH-001​
18,1​
20,1​
2​
100,0​
6,4​
7,25​
0,85​
94,1​
13​
DH-001​
20,1​
22,1​
2​
100,0​
7,25​
8,05​
0,8​
100,0​
14​
DH-001​
22,1​
24,1​
2​
100,0​
8,05​
9,2​
1,15​
100,0​
15​
9,2​
10,3​
1,1​
90,9​
16​
10,3​
11,3​
1​
100,0​
17​
11,3​
12,8​
1,5​
96,7​
18​
12,8​
14,3​
1,5​
100,0​
19​
14,3​
15,8​
1,5​
100,0​
20​
15,8​
17,3​
1,5​
100,0​
21​
17,3​
18,8​
1,5​
100,0​
22​
18,8​
19,9​
1,1​
100,0​
23​
19,9​
20,6​
0,7​
100,0​
24​
20,6​
21,4​
0,8​
100,0​
25​
21,4​
22,9​
1,5​
100,0​
26​
22,9​
24,1​
1,2​
100,0​

Array formula in E3 copied down
=SUM(IF(1-((B3>$H$3:$H$26)+($G$3:$G$26>C3)),(IF($H$3:$H$26>C3,C3,$H$3:$H$26)-IF($G$3:$G$26>B3,$G$3:$G$26,B3))*$J$3:$J$26))/D3

confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.
 
Upvote 0
Marcelo Branco said:
Maybe this...


A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
1​
Sampling intervals​
Recovery​
2​
Hole #​
From​
To​
length​
Adjusted recovery​
From​
To​
length​
Recovery (%)​
3​
DH-001​
0​
0,4​
0,4​
100,0​
0​
0,7​
0,7​
100,0​
4​
DH-001​
0,4​
3,4​
3​
71,7​
0,7​
1,9​
1,2​
45,8​
5​
DH-001​
3,4​
6​
2,6​
80,9​
1,9​
2,5​
0,6​
66,7​
6​
DH-001​
6​
8,1​
2,1​
96,5​
2,5​
3​
0,5​
100,0​
7​
DH-001​
8,1​
10,1​
2​
95,9​
3​
3,4​
0,4​
100,0​
8​
DH-001​
10,1​
12,1​
2​
97,8​
3,4​
4,05​
0,65​
96,9​
9​
DH-001​
12,1​
14,1​
2​
98,8​
4,05​
4,7​
0,65​
53,8​
10​
DH-001​
14,1​
16,1​
2​
100,0​
4,7​
5,55​
0,85​
82,4​
11​
DH-001​
16,1​
18,1​
2​
100,0​
5,55​
6,4​
0,85​
94,1​
12​
DH-001​
18,1​
20,1​
2​
100,0​
6,4​
7,25​
0,85​
94,1​
13​
DH-001​
20,1​
22,1​
2​
100,0​
7,25​
8,05​
0,8​
100,0​
14​
DH-001​
22,1​
24,1​
2​
100,0​
8,05​
9,2​
1,15​
100,0​
15​
9,2​
10,3​
1,1​
90,9​
16​
10,3​
11,3​
1​
100,0​
17​
11,3​
12,8​
1,5​
96,7​
18​
12,8​
14,3​
1,5​
100,0​
19​
14,3​
15,8​
1,5​
100,0​
20​
15,8​
17,3​
1,5​
100,0​
21​
17,3​
18,8​
1,5​
100,0​
22​
18,8​
19,9​
1,1​
100,0​
23​
19,9​
20,6​
0,7​
100,0​
24​
20,6​
21,4​
0,8​
100,0​
25​
21,4​
22,9​
1,5​
100,0​
26​
22,9​
24,1​
1,2​
100,0​

<tbody>
</tbody>


Array formula in E3 copied down
=SUM(IF(1-((B3>$H$3:$H$26)+($G$3:$G$26>C3)),(IF($H$3:$H$26>C3,C3,$H$3:$H$26)-IF($G$3:$G$26>B3,$G$3:$G$26,B3))*$J$3:$J$26))/D3

confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.
Click to expand...


Marcelo thank you so much! the math checks out.

The only problem is that I only supplied you with 26 records and there is thousands. Each individual hole is about 200 records. So when one hole is complete then the array code would want to skip onto the next hole. But I'm happy with this and will do them manually for now!
 
Last edited:
Upvote 0
You are welcome. Glad to help.

Hope you can adapt the formula for your real scenario.

M.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,480
Messages
6,125,047
Members
449,206
Latest member
Healthydogs

Share this page

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
Back
Top