Each row of data represents a test drill sample, with the hole name, its x/y coordinates, various other data, and the sample value and a code revealing if baserock has been hit. Yuo drill down until two '107's tell you it's bottomed out. Then you reverse up the hole averaging as you go until you hit the data for the next hole...
Any spreadsheet can import/export the data in comma-separated or space-separated or any other chosen form. As given here I use the space-separated form. The data is read into an array, and word$( is used to separate the needed two numeric columns.
816475 612556.66 206700.69 695.7 0 1 1 0.55 2 816475 612556.66 206700.69 694.7 1 2 1 0.76 2 816475 612556.66 206700.69 693.7 2 3 1 0.29 97 816475 612556.66 206700.69 692.7 3 4.2 1.2 0.27 97 816478 612657.66 206699.49 687.6 0 1 1 0.31 1 816478 612657.66 206699.49 686.6 1 2 1 0.39 1 816478 612657.66 206699.49 685.6 2 3 1 0.82 2 816478 612657.66 206699.49 684.6 3 4 1 0.96 17 816478 612657.66 206699.49 683.6 4 5 1 1.01 2 816478 612657.66 206699.49 682.6 5 6 1 1.1 3 816478 612657.66 206699.49 681.6 6 7 1 1.11 3 816478 612657.66 206699.49 680.6 7 8 1 1.12 3 816478 612657.66 206699.49 679.6 8 9 1 0.24 107 816478 612657.66 206699.49 678.6 9 10 1 0.2 107 816512 612755.44 206900.48 684 0 1 1 0.41 1 816512 612755.44 206900.48 683 1 2 1 0.49 2 816512 612755.44 206900.48 682 2 3 1 0.68 2 816512 612755.44 206900.48 681 3 4 1 1.07 3 816512 612755.44 206900.48 680 4 5 1 1.26 7 816512 612755.44 206900.48 679 5 6 1 1.18 3 816512 612755.44 206900.48 678 6 7 1 1.29 3 816512 612755.44 206900.48 677 7 7.5 0.5 1.22 3 816512 612755.44 206900.48 676.5 7.5 8.5 1 0.25 107 816512 612755.44 206900.48 675.5 8.5 9.5 1 0.24 107 816515 612858.25 206899.19 682.6 0 1 1 0.39 1 816515 612858.25 206899.19 681.6 1 2 1 0.63 2 816515 612858.25 206899.19 680.6 2 3 1 0.62 2 816515 612858.25 206899.19 679.6 3 4 1 1.1 2 816515 612858.25 206899.19 678.6 4 5 1 1.02 107 816515 612858.25 206899.19 677.6 5 6 1 0.93 3 816515 612858.25 206899.19 676.6 6 7 1 1.63 3 816515 612858.25 206899.19 675.6 7 8 1 1.59 3 816515 612858.25 206899.19 674.6 8 8.5 0.5 1.46 3 816515 612858.25 206899.19 674.1 8.5 9.5 1 0.23 107 816515 612858.25 206899.19 673.1 9.5 10.5 1 0.22 107 816518 612954.44 206898.89 677.8 0 1 1 0.66 1 816518 612954.44 206898.89 676.8 1 2 1 0.69 2 816518 612954.44 206898.89 675.8 2 3 1 0.68 2 816518 612954.44 206898.89 674.8 3 4 1 0.91 3 816518 612954.44 206898.89 673.8 4 5 1 1.11 3 816518 612954.44 206898.89 672.8 5 6 1 0.18 3 816518 612954.44 206898.89 671.8 6 7 1 0.18 3 816542 612758.25 206809.3 687.2 0 1 1 0.69 1 816542 612758.25 206809.3 686.2 1 2 1 0.73 2 816542 612758.25 206809.3 685.2 2 3 1 0.94 2 816542 612758.25 206809.3 684.2 3 4 1 1.07 2 816542 612758.25 206809.3 683.2 4 5 1 1.07 2 816542 612758.25 206809.3 682.2 5 6 1 1.04 2 816542 612758.25 206809.3 681.2 6 7 1 1.1 3 816542 612758.25 206809.3 680.2 7 8 1 0.27 107 816542 612758.25 206809.3 679.2 8 9 1 0.29 107
' It's a new hole if you see two consecutive 107s in column 9. Otherwise skip.
' Now average up to end-of-hole
' Data is in a space-separated file.
' count the number of records in the file
countdata = 0
open "sample2.spsv" for input as #count
while eof( #count) = 0
line input #count, n$
countdata = countdata + 1
wend
close #count
dim existingRows$( countdata) ' store each ROW as a single array entry
open "sample2.spsv" for input as #readme
for i = 1 to countdata ' so first line read appears at top of data
line input #readme, data$ ': print i, data$ ' to check data read correctly
existingRows$( i) =data$
next
close #readme
print "Data successfully read": print
print "Hole number "; word$( existingRows$( countdata), 1, chr$( 32))
lastHoleLookedAt$ =word$( existingRows$( countdata), countdata, chr$( 32))
oreValTotal =0
numSamples =0
for i = countdata to 1 step -1
currentHole$ =word$( existingRows$( i), 1, chr$( 32))
if currentHole$ <>lastHoleLookedAt$ then ' we've just met a new set of readings & hole name...
if i <>countdata and numSamples <>0 then ' print final results of last hole. Avoid div-by-zero error if no values
ave =oreValTotal /numSamples
print "Hole number '"; lastHoleLookedAt$; "' has average "; using( "###.#####", ave)
print: print: print "Hole "; currentHole$
oreValTotal =0 ' reset for new hole
numSamples =0
end if
i =i -1
' skip hole if no 107 to base it from and not EOD
if val( word$( existingRows$( i), 9, chr$( 32))) <10 and ( i <>countdata) then
print "Skip hole '"; currentHole$; "'": print: print
'i =i -1
end if
lastHoleLookedAt$ =currentHole$
else ' work upwards on new hole
Code =val( word$( existingRows$( i), 9, chr$( 32)))
'if Code <90 then
newOreVal =val( word$( existingRows$( i), 8, chr$( 32)))
oreValTotal =oreValTotal +newOreVal
numSamples =numSamples +1
print "Total ore value "; using( "##.###", oreValTotal); " with "; numSamples;_
" samples.", "Ave. "; using( "##.####", oreValTotal /numSamples)
'end if
end if
if i =1 then ' special case- last (top) row
ave =oreValTotal /numSamples
print "Hole number '"; lastHoleLookedAt$; "' has average "; using( "###.#####", ave)
end if
scan
next i
'open "processedOreData.csv" for output as #outfile
'close #outfile
end