# multiple conditions then a find average but incrementaly

#### alanlambden

##### Board Regular
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

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Excel 2010
ABCDEFGHIJKL
1Hole_IDXYZFromToLengthOreCodeAverage ore grade by depth
2104442606451.2201099.7659.50110.9721.241.24
3104442606451.2201099.7658.51211.2721.2941.294
4104442606451.2201099.7657.52311.3631.31.3
5104442606451.2201099.7656.53411.3341.281.28
6104442606451.2201099.7655.54511.2841.2551.255
7104442606451.2201099.7654.555.50.51.2341.231.23
8104442606451.2201099.76545.56.510.3110
9104442606451.2201099.76536.57.510.2210
10104445606551.3201099.36560111.3821.21.2
11104445606551.3201099.36551211.421.111.11
12104445606551.3201099.365422.50.50.8240.820.82
13104445606551.3201099.3653.52.53.510.3610
14104445606551.3201099.3652.53.54.510.3110
Sheet10 (2)
Cell Formulas
RangeFormula

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

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

Is this any better?

Excel 2010
ABCDEFGHIJK
1Hole_IDXYZFromToLengthOreCodeAverage ore grade by depth
2104442606451.2201099.7659.50110.9721.241.24
3104442606451.2201099.7658.51211.2721.2941.294
4104442606451.2201099.7657.52311.3631.31.3
5104442606451.2201099.7656.53411.3341.281.28
6104442606451.2201099.7655.54511.2841.2551.255
7104442606451.2201099.7654.555.50.51.2341.231.23
8104442606451.2201099.76545.56.510.3110
9104442606451.2201099.76536.57.510.2210
10104445606551.3201099.36560111.3821.21.2
11104445606551.3201099.36551211.421.111.11
12104445606551.3201099.365422.50.50.8240.820.82
13104445606551.3201099.3653.52.53.510.3610
14104445606551.3201099.3652.53.54.510.3110
Sheet10 (4)
Cell Formulas
RangeFormula
K2=IF(I2=10,"",AVERAGE(H2:OFFSET(H2,COUNTIFS(\$A2:\$A\$14,A2,\$I2:\$I\$14,"<>10")-1,)))

Or:

Excel 2010
ABCDEFGHIJK
1Hole_IDXYZFromToLengthOreCodeAverage ore grade by depth
2104442606451.2201099.7659.50110.9721.241.24
3104442606451.2201099.7658.51211.2721.2941.294
4104442606451.2201099.7657.52311.3631.31.3
5104442606451.2201099.7656.53411.3341.281.28
6104442606451.2201099.7655.54511.2841.2551.255
7104442606451.2201099.7654.555.50.51.2341.231.23
8104442606451.2201099.76545.56.510.3110
9104442606451.2201099.76536.57.510.2210
10104445606551.3201099.36560111.3821.21.2
11104445606551.3201099.36551211.421.111.11
12104445606551.3201099.365422.50.50.8240.820.82
13104445606551.3201099.3653.52.53.510.3610
14104445606551.3201099.3652.53.54.510.3110
Sheet10 (5)
Cell Formulas
RangeFormula
K2=IF(I2=10,"",AVERAGE(H2:INDEX(H:H,ROW()+COUNTIFS(\$A2:\$A\$14,A2,\$I2:\$I\$14,"<>10")-1)))

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,)))

Like this?

Excel 2010
ABCDEFGHIJK
1Hole_IDXYZFromToLengthOreCodeAverage ore grade by depth
2104442606451.2201099.7659.50110.9721.241.1375
3104442606451.2201099.7658.51211.2721.2941.171
4104442606451.2201099.7657.52311.3631.31.14625
5104442606451.2201099.7656.53411.3341.281.075
6104442606451.2201099.7655.54511.2841.2550.9475
7104442606451.2201099.7654.555.50.51.2341.230.615
8104442606451.2201099.76545.56.510.3110
9104442606451.2201099.76536.57.510.2210
10104445606551.3201099.36560111.3821.21.063333333
11104445606551.3201099.36551211.421.110.905
12104445606551.3201099.365422.50.50.8240.820.41
13104445606551.3201099.3653.52.53.510.3610
14104445606551.3201099.3652.53.54.510.3110
Sheet10 (6)
Cell Formulas
RangeFormula
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.

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!

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
ABCDEFGHIJK
1Hole_IDXYZFromToLengthOreCodeAverage ore grade by depth
2104442606451.2201099.7659.50110.9721.241.24
3104442606451.2201099.7658.51211.2721.2941.294
4104442606451.2201099.7657.52311.3631.31.3
5104442606451.2201099.7656.53411.33101.281.28
6104442606451.2201099.7655.54511.2841.2551.255
7104442606451.2201099.7654.555.50.51.2341.231.23
8104442606451.2201099.76545.56.510.3110
9104442606451.2201099.76536.57.510.2210
10104445606551.3201099.36560111.3821.21.2
11104445606551.3201099.36551211.421.111.11
12104445606551.3201099.365422.50.50.8240.820.82
13104445606551.3201099.3653.52.53.510.3610
14104445606551.3201099.3652.53.54.510.3110

</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)))

Does this work?

Excel 2010
ABCDEFGHIJKL
1Hole_IDXYZFromToLengthOreCodeAverage ore grade by depth
2104442606451.2201099.7659.50110.9721.241.222
3104442606451.2201099.7658.51211.2721.2941.285
4104442606451.2201099.7657.52311.3631.31.29
5104442606451.2201099.7656.53411.33101.28
6104442606451.2201099.7655.54511.2841.2551.255
7104442606451.2201099.7654.555.50.51.2341.231.23
8104442606451.2201099.76545.56.510.3110
9104442606451.2201099.76536.57.510.2210
10104445606551.3201099.36560111.3821.21.2
11104445606551.3201099.36551211.421.111.11
12104445606551.3201099.365422.50.50.8240.820.82
13104445606551.3201099.3653.52.53.510.3610
14104445606551.3201099.3652.53.54.510.3110
Sheet6
Cell Formulas
RangeFormula
L2=IFERROR(AVERAGEIFS(\$H2:\$H\$14,\$A2:\$A\$14,A2,\$I2:\$I\$14,"<>10",\$I2:\$I\$14,">="&I2),"")

Replies
3
Views
222
Replies
7
Views
355
Replies
2
Views
176
Replies
5
Views
382
Replies
3
Views
245

1,196,235
Messages
6,014,146
Members
441,807
Latest member
sjkenjalo

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

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