multiple conditions then a find average but incrementaly

[alanlambden]

Hi Guys,

Im working on a mining project where there is a DB full of drillholes. Each sample in the drillhole is either .5m or 1m in length. Each samp,e also has a code assigned to it depending on what rock type it is. Anything labelled with "10" means bedrock and anything between 1-6 is ore. I need to figure out what the average ore grade is as a function of depth. I need to populate a table showing the average ore grade, starting at the bedrock contact and working upwards. The average grade should show the average at 1m above bedrock, 2m above bedrock and so on. Below is an example or the data and the desired result:

Hole_ID	X	Y	Z	From	To	Length	Ore	Code	Average ore grade by depth
104442	606451.2	201099.7	659.5	0	1	1	0.97	2	1.24
104442	606451.2	201099.7	658.5	1	2	1	1.27	2	1.294
104442	606451.2	201099.7	657.5	2	3	1	1.36	3	1.3
104442	606451.2	201099.7	656.5	3	4	1	1.33	4	1.28
104442	606451.2	201099.7	655.5	4	5	1	1.28	4	1.255
104442	606451.2	201099.7	654.5	5	5.5	0.5	1.23	4	1.23
104442	606451.2	201099.7	654	5.5	6.5	1	0.31	10
104442	606451.2	201099.7	653	6.5	7.5	1	0.22	10
104445	606551.3	201099.3	656	0	1	1	1.38	2	1.2
104445	606551.3	201099.3	655	1	2	1	1.4	2	1.11
104445	606551.3	201099.3	654	2	2.5	0.5	0.82	4	0.82
104445	606551.3	201099.3	653.5	2.5	3.5	1	0.36	10
104445	606551.3	201099.3	652.5	3.5	4.5	1	0.31	10

<colgroup><col span="9"><col></colgroup><tbody>
</tbody>

If anyone has any commands that would be useful for this task it would be greatly appreciated

Thanks

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
1	Hole_ID	X	Y	Z	From	To	Length	Ore	Code	Average ore grade by depth
2	104442	606451.2	201099.7	659.5	0	1	1	0.97	2	1.24		1.24
3	104442	606451.2	201099.7	658.5	1	2	1	1.27	2	1.294		1.294
4	104442	606451.2	201099.7	657.5	2	3	1	1.36	3	1.3		1.3
5	104442	606451.2	201099.7	656.5	3	4	1	1.33	4	1.28		1.28
6	104442	606451.2	201099.7	655.5	4	5	1	1.28	4	1.255		1.255
7	104442	606451.2	201099.7	654.5	5	5.5	0.5	1.23	4	1.23		1.23
8	104442	606451.2	201099.7	654	5.5	6.5	1	0.31	10
9	104442	606451.2	201099.7	653	6.5	7.5	1	0.22	10
10	104445	606551.3	201099.3	656	0	1	1	1.38	2	1.2		1.2
11	104445	606551.3	201099.3	655	1	2	1	1.4	2	1.11		1.11
12	104445	606551.3	201099.3	654	2	2.5	0.5	0.82	4	0.82		0.82
13	104445	606551.3	201099.3	653.5	2.5	3.5	1	0.36	10
14	104445	606551.3	201099.3	652.5	3.5	4.5	1	0.31	10
Sheet10 (2)

Cell Formulas
Range	Formula
L2	=IF(I2=10,"",AVERAGE(INDIRECT(ADDRESS(ROW(),8)&":"&ADDRESS(ROW()+SUMPRODUCT(--($A2:$A$14=A2),--($I2:$I$14<>10))-1,8))))

Index is less volatile than Indirect, so it may be better to switch depending on how large the data set

 

[alanlambden]

Index is less volatile than Indirect, so it may be better to switch depending on how large the data set

Thanks sheetspread. My DB is a total of 200,000+ rows of data which is excruciatingly slow. Ive split the DB into 4 separate files to try run it quicker. I'm always mindful there is a better solution though

 

[sheetspread]

Is this any better?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	Hole_ID	X	Y	Z	From	To	Length	Ore	Code	Average ore grade by depth
2	104442	606451.2	201099.7	659.5	0	1	1	0.97	2	1.24	1.24
3	104442	606451.2	201099.7	658.5	1	2	1	1.27	2	1.294	1.294
4	104442	606451.2	201099.7	657.5	2	3	1	1.36	3	1.3	1.3
5	104442	606451.2	201099.7	656.5	3	4	1	1.33	4	1.28	1.28
6	104442	606451.2	201099.7	655.5	4	5	1	1.28	4	1.255	1.255
7	104442	606451.2	201099.7	654.5	5	5.5	0.5	1.23	4	1.23	1.23
8	104442	606451.2	201099.7	654	5.5	6.5	1	0.31	10
9	104442	606451.2	201099.7	653	6.5	7.5	1	0.22	10
10	104445	606551.3	201099.3	656	0	1	1	1.38	2	1.2	1.2
11	104445	606551.3	201099.3	655	1	2	1	1.4	2	1.11	1.11
12	104445	606551.3	201099.3	654	2	2.5	0.5	0.82	4	0.82	0.82
13	104445	606551.3	201099.3	653.5	2.5	3.5	1	0.36	10
14	104445	606551.3	201099.3	652.5	3.5	4.5	1	0.31	10
Sheet10 (4)

Cell Formulas
Range	Formula
K2	=IF(I2=10,"",AVERAGE(H2:OFFSET(H2,COUNTIFS($A2:$A$14,A2,$I2:$I$14,"<>10")-1,)))

 

[sheetspread]

Or:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	Hole_ID	X	Y	Z	From	To	Length	Ore	Code	Average ore grade by depth
2	104442	606451.2	201099.7	659.5	0	1	1	0.97	2	1.24	1.24
3	104442	606451.2	201099.7	658.5	1	2	1	1.27	2	1.294	1.294
4	104442	606451.2	201099.7	657.5	2	3	1	1.36	3	1.3	1.3
5	104442	606451.2	201099.7	656.5	3	4	1	1.33	4	1.28	1.28
6	104442	606451.2	201099.7	655.5	4	5	1	1.28	4	1.255	1.255
7	104442	606451.2	201099.7	654.5	5	5.5	0.5	1.23	4	1.23	1.23
8	104442	606451.2	201099.7	654	5.5	6.5	1	0.31	10
9	104442	606451.2	201099.7	653	6.5	7.5	1	0.22	10
10	104445	606551.3	201099.3	656	0	1	1	1.38	2	1.2	1.2
11	104445	606551.3	201099.3	655	1	2	1	1.4	2	1.11	1.11
12	104445	606551.3	201099.3	654	2	2.5	0.5	0.82	4	0.82	0.82
13	104445	606551.3	201099.3	653.5	2.5	3.5	1	0.36	10
14	104445	606551.3	201099.3	652.5	3.5	4.5	1	0.31	10
Sheet10 (5)

Cell Formulas
Range	Formula
K2	=IF(I2=10,"",AVERAGE(H2:INDEX(H:H,ROW()+COUNTIFS($A2:$A$14,A2,$I2:$I$14,"<>10")-1)))

 

[alanlambden]

Thanks sheetpread, these work great and will be a huge help in the future. I may have overseen an error though. The average must be weighted against the length. So if the length of a sample is 1m and another is .5m, the .5m must get half the weighted average. Im trying to make the necessary adjustments using this formula but am getting an error:

=IF(I2=10,"",AVERAGE(H2*G2:OFFSET(H2,COUNTIFS($A2:$A$14,A2,$I2:$I$14,"<>10")-1,)))

 

[sheetspread]

Like this?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	Hole_ID	X	Y	Z	From	To	Length	Ore	Code	Average ore grade by depth
2	104442	606451.2	201099.7	659.5	0	1	1	0.97	2	1.24	1.1375
3	104442	606451.2	201099.7	658.5	1	2	1	1.27	2	1.294	1.171
4	104442	606451.2	201099.7	657.5	2	3	1	1.36	3	1.3	1.14625
5	104442	606451.2	201099.7	656.5	3	4	1	1.33	4	1.28	1.075
6	104442	606451.2	201099.7	655.5	4	5	1	1.28	4	1.255	0.9475
7	104442	606451.2	201099.7	654.5	5	5.5	0.5	1.23	4	1.23	0.615
8	104442	606451.2	201099.7	654	5.5	6.5	1	0.31	10
9	104442	606451.2	201099.7	653	6.5	7.5	1	0.22	10
10	104445	606551.3	201099.3	656	0	1	1	1.38	2	1.2	1.063333333
11	104445	606551.3	201099.3	655	1	2	1	1.4	2	1.11	0.905
12	104445	606551.3	201099.3	654	2	2.5	0.5	0.82	4	0.82	0.41
13	104445	606551.3	201099.3	653.5	2.5	3.5	1	0.36	10
14	104445	606551.3	201099.3	652.5	3.5	4.5	1	0.31	10
Sheet10 (6)

Cell Formulas
Range	Formula
K2	{=IF(I2=10,"",AVERAGE(H2:INDEX(H:H,ROW()+COUNTIFS($A2:$A$14,A2,$I2:$I$14,"<>10")-1)*G2:INDEX(G:G,ROW()+COUNTIFS($A2:$A$14,A2,$I2:$I$14,"<>10")-1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[alanlambden]

Thank you Sheetspread you have been a massive help. I figured out why the calculations were taking so long...Because i had 200000+ rows of data, the countifs command cycled through every cell to check that the condition was satisfied. Knowing that my data was sorted accordingly and each unique hole_id was never more than 40 rows each I could shorten the array size to 50 cells rather than the entire workbook. This made the calculations almost instant with the same results. Good to know in the future.

Thanks mate!

 

[alanlambden]

Hi,

I have another issue with this formula. Sometimes there are floating 10 values up hole that i need to ignore as these would not be bedrock. See an edited version of my table below where i inputed the 10 in row 5. If this 10 is not a clear bedrock contact at the bottom of a hole, then i want it ignored. The code im using below works best but im unsure how to code exceptions. Thank you

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K
1	Hole_ID	X	Y	Z	From	To	Length	Ore	Code	Average ore grade by depth
2	104442	606451.2	201099.7	659.5	0	1	1	0.97	2	1.24	1.24
3	104442	606451.2	201099.7	658.5	1	2	1	1.27	2	1.294	1.294
4	104442	606451.2	201099.7	657.5	2	3	1	1.36	3	1.3	1.3
5	104442	606451.2	201099.7	656.5	3	4	1	1.33	10	1.28	1.28
6	104442	606451.2	201099.7	655.5	4	5	1	1.28	4	1.255	1.255
7	104442	606451.2	201099.7	654.5	5	5.5	0.5	1.23	4	1.23	1.23
8	104442	606451.2	201099.7	654	5.5	6.5	1	0.31	10
9	104442	606451.2	201099.7	653	6.5	7.5	1	0.22	10
10	104445	606551.3	201099.3	656	0	1	1	1.38	2	1.2	1.2
11	104445	606551.3	201099.3	655	1	2	1	1.4	2	1.11	1.11
12	104445	606551.3	201099.3	654	2	2.5	0.5	0.82	4	0.82	0.82
13	104445	606551.3	201099.3	653.5	2.5	3.5	1	0.36	10
14	104445	606551.3	201099.3	652.5	3.5	4.5	1	0.31	10

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

=IF(I2=10 (except when the 10 is not sequential to the bottom of the hole),"",AVERAGE(H2:INDEX(H:H,ROW()+COUNTIFS($A2:$A$14,A2,$I2:$I$14,"<>10")-1)))

 

[sheetspread]

Does this work?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
1	Hole_ID	X	Y	Z	From	To	Length	Ore	Code	Average ore grade by depth
2	104442	606451.2	201099.7	659.5	0	1	1	0.97	2	1.24		1.222
3	104442	606451.2	201099.7	658.5	1	2	1	1.27	2	1.294		1.285
4	104442	606451.2	201099.7	657.5	2	3	1	1.36	3	1.3		1.29
5	104442	606451.2	201099.7	656.5	3	4	1	1.33	10	1.28
6	104442	606451.2	201099.7	655.5	4	5	1	1.28	4	1.255		1.255
7	104442	606451.2	201099.7	654.5	5	5.5	0.5	1.23	4	1.23		1.23
8	104442	606451.2	201099.7	654	5.5	6.5	1	0.31	10
9	104442	606451.2	201099.7	653	6.5	7.5	1	0.22	10
10	104445	606551.3	201099.3	656	0	1	1	1.38	2	1.2		1.2
11	104445	606551.3	201099.3	655	1	2	1	1.4	2	1.11		1.11
12	104445	606551.3	201099.3	654	2	2.5	0.5	0.82	4	0.82		0.82
13	104445	606551.3	201099.3	653.5	2.5	3.5	1	0.36	10
14	104445	606551.3	201099.3	652.5	3.5	4.5	1	0.31	10
Sheet6

Cell Formulas
Range	Formula
L2	=IFERROR(AVERAGEIFS($H2:$H$14,$A2:$A$14,A2,$I2:$I$14,"<>10",$I2:$I$14,">="&I2),"")