Depleting from multiple values at once

[graphicgoose]

Hi,

I've inherited a spreadsheet that is used for tracking how many tonnes of dirt has been mined/removed from a stope (a stope is a void/hole in an underground mine). Stopes are broken up into firings that are blasted at different times. The purpose of this section of the spreadsheet is to track how much of the planned firings have been trucked (depleted), how much of each firing is remaining, and how much of the material that has been trucked is overbreak (overbreak is essentially extra material that has fallen into the stope, making the void larger than planned). The problem is that each firing may not be fully depleted before the next firing is blasted on top of the remaining dirt from the previous firing. At which point the depletions work backwards, e.g. if Firing 1 has 519 tonnes of dirt remaining (undepleted) when Firing 2 is dropped on top of the remains of Firing 1, then once Firing 2 has been fully depleted depletion will resume of Firing 1. This process gets rather complex when there are multiple firings in a stope and each firing isn't depleted before the next firing is blasted. I have attached an example that contains 9 firings. The attached sheet has three worksheets, one entitled "Original" which is the sheet as I inherited it and one entitled "New" which is where I've got to with my changes, and then one entitled "Test" in which I was playing with an idea.

The sheet, as I inherited it, has these depletions as a manual process. The cells in column J contain the following formula:

=IF(D6="","",(D6))-G7-G9-249-270

As you can see by the formula the user has to manually add in each depletion. This process is done for each firing until each firing is fully depleted (i.e. =0). This can also be evidenced by the Overbreak total in column D4 which contains the following formula:

=360+490+G59+G61+812+G137+G139+G141+G145+G147+G149+986

The only way the amount of overbreak attributed to each days trucking is recorded is in the "Comments" column.

If a firing has been depleted to 0 but more dirt is still coming out the user has to attribute the extra dirt to previous firings that haven't been fully depleted or overbreak. The user then has to calculate the weighted grade of the dirt trucked that day using the "Calculator", which is essentially a sumproduct of the amounts of each dirt attributed to each firing. This is the grade that is then entered into Column H for that day.

What I'm trying to do is automate this whole process so that all the user needs to do is input the trucking tonnes in column G.

So far I've used the following formula in column J for the depletions:

=IF(C4="","",IFERROR(IF(D4-SUM(G5:INDEX(C5:$G$420,MATCH("*",C5:$C$420,0),5))<=0,0,D4-SUM(G5:INDEX(C5:$G$420,MATCH("*",C5:$C$420,0),5))),IF(D4-SUM(G5:$G$420)<=0,0,D4-SUM(G5:$G$420))))

And I've used this formula in column M for the overbreak:

=IF(ISBLANK(C4),"",IFERROR(IF(D4-SUM(G5:INDEX(C5:$G$420,MATCH("*",C5:$C$420,0),5))<=0,SUM(G5:INDEX(C5:$G$420,MATCH("*",C5:$C$420,0),5))-D4,""),IF(SUM(G5:$G$420)-D4<=0,"",SUM(G5:$G$420)-D4)))

The problem with this approach is that the Depletion and Overbreak summaries don't balance properly.

I had a bit of a play around using some interim working columns, which I've included in the "Test" worksheet, in which I could get the Depletion to balance out correctly...but I couldn't quite work out the formula to use for the overbreak in column S.

Even with a working formula in column S I still require a way to automate the calculation of the grades in columns H, column N and column T that would normally be done manually each day using the "Calculator".

If anyone has a solution to my issue I would be greatly appreciative!

Thanks,
Daniel

[graphicgoose]

Bump

If anyone has a simpler/smarter approach I'm also open to that.

[graphicgoose]

Another friendly bump

[Saarang84]

Hi Daniel,

Your workbook layout makes it difficult for you to code the formula for calculations, you might consider to change the layout of your workbook. Probably, you can capture all information on one worksheet and summarize and do the calculations on another adjacent worksheet.

Let me work on the layout from the information you've described so far and design a layout and come back to you in case if I've got more questions.

By the way, why are static numbers being used in the formulas that already exist? Where are those numbers coming from?

[graphicgoose]

Hi Sarang,

Thanks for looking into this for me. The issue with summarizing and doing the calculations on another worksheet is that there are multiple sheets in the actual workbook (I've only included one example in the file I uploaded, to keep it simple) as we will have multiple stopes being fired/trucked at any one point in time, as well as sheets for completed/finished stopes in the workbook also. So this is something that will be needed to implement across multiple sheets.

I'm interested to see what you come up with for the layout, as I've had a bit of a think of how to change it but am struggling to think what would work best.

The use of static numbers in the formula is my very reason for wanting to redesign and automate the spreadsheet. The original spreadsheets creator designed it this way, which is rather laborious and time consuming, not to mention confusing and prone to error and mistakes. The static numbers in the formulas are generated from the values in column G, however these won't always be the total amount of the value in the cell in column G. For example, in cell J6 you can see cell G7 and G9 have been depleted from F1 (cell D6) but the 249 comes from part of the 450 tonnes in cell G37 and the 270 comes from part of the 630 tonnes in cell G39. The reason this comes about is that, in the case of the 249 value, on that particular day (5/02/2011, cell G37) there would have only been 201 tonnes remaining in cell J13 so the user minuses the 201 from J13 and the remaining tonnes for that day (i.e. 249) are minused from J6. These are the values that are in the "Calculator" at the top right of the sheet, as this is essentially how the value in cell H37 was calculated...which is another thing I would like to automate. This info is captured in the comment in cell M37, which is essentially saying that on the 5/02/2011 of the 450 tonnes trucked 201 tonnes of that was depleted from firing 2 (F2) and 249 tonnes of it was depleted from firing 1 (F1).

This process continues working backwards until there are no tonnes remaining in any of the previous firings, at which point any excess gets attributed as Overbreak. This can be seen by looking at the information from the 6/02/2011. On this day 630 tonnes were trucked. By this stage there were no tonnes remaining to deplete from firing 2 (i.e. cell J13 was = 0) and there were only 270 tonnes remaining to deplete from firing 1 (i.e. cell J6 was = 270). Therefore on this day 270 tonnes were depleted from firing 1 and the remaining 360 tonnes from that day were attributed to overbreak. Again, this has been captured with the comment in cell M39.

I hope that makes sense, the whole process has been designed horribly by the original sheet's creator and I'm hoping, with help from those more experienced than myself, to greatly improve the process and automate it as much as possible. Ideally I'd like for the only value that the user needs to input is those values in column G (which I'll have a macro to import anyway) and have everything else auto calculate.

[Saarang84]

The issue with summarizing and doing the calculations on another worksheet is that there are multiple sheets in the actual workbook (I've only included one example in the file I uploaded, to keep it simple) as we will have multiple stopes being fired/trucked at any one point in time, as well as sheets for completed/finished stopes in the workbook also. So this is something that will be needed to implement across multiple sheets.

Hi Daniel,

Does the layout of the data sheets vary across the entire workbook? If so, can you upload the full template that is currently used ? This would help me in the design of the layout of the worksheet used for calculations. When you do upload the whole workbook, do take care to remove sensitive information. Else mock up some data in it if you can.

[graphicgoose]

Hi Sarang,

I've attached a workbook that gives you an idea of the total layout. I've put in 1 example sheet plus the template and have populated the example sheet with some random data that shows you how the sheet works. We may have 5 (or more) active stopes at any one time (meaning there would be 5 or more active sheets just like the example sheet), plus prior completed stopes still remaining in the sheet. This means the depletions needs to be calculated individually for each active stope each day. The sheet I uploaded in my original post relates to columns M:Y in the sheets in the workbook attached to this post. Please bear in mind that columns CZ:DT contain graphs...but my file was too large to upload so I've had to cut a few things out, which is why I've only included one mock stope sheet.

Regards,
Daniel

[xladept]

Hi Goose,

I ran this on your Test Sheet - I left most of the formulae in the sheet (not my usual habit) - Let me know if what I've done is correct:

Sub GraphicGoose(): Dim Firing As String, ST As Single, AU As Single, T, WDO As Single, OT As Single
        Dim i As Long, j As Long, er As Long, ws As Worksheet, WF As WorksheetFunction
        Set ws = ActiveSheet: Set WF = WorksheetFunction: Const a = 31.1034777: i = 6
                er = ws.Rows.Find("*", , , , xlByRows, xlPrevious).Row
FireUp:      Do:       j = i + 1: Do Until Cells(j, 3) <> "" Or j >= er: j = j + 1: Loop
                      
                Firing = Cells(i, 3): ST = Cells(i, 4): AU = Cells(i, 5)
                If Firing = "" Then Exit Sub
                'If ST * AU <> 0 Then Cells(i, 6) = ST * AU / a
                T = WF.Sum(Range(ws.Cells(i, 7), ws.Cells(j - 1, 7))): WDO = ST - T
                If WDO > 0 Then
                Cells(i, 10) = WDO: Cells(i, 13) = 0: Cells(i, 16) = T
                Else: Cells(i, 13) = Abs(WDO): Cells(i, 10) = 0: Cells(i, 19) = Abs(T): End If
            i = j
            Loop
            Calculate
End Sub

[graphicgoose]

Hi XLAdept, thanks for the reply.

I tested the macro but unfortunately it doesn't produce the intended result. I've attached the original sheet, in which I've run your macro in the "test" worksheet. I've also ammended the "Original" worksheet to reflect the desired outcome for the overbreak columns, rather than the overbreak just being noted in the "Comments". Essentially, I would like the end result to match the numbers in the "Original" sheet of the workbook.

Thanks!

[xladept]

Hi Goose,

Since my last post I acquired a gig - don't know when I can get back to you

[graphicgoose]

No worries XLAdept, thanks for letting me know. Whenever you get a chance Sarang's working on something too, so see what we come up with

[xladept]

Hi Goose,

In D4 you've overwritten the sum of the firings - I don't get it! Can you describe how the numbers should be calculated?

[graphicgoose]

Hi xladept,

Sorry, that value in D4 in my the first workbook I posted was a total for the Overbreak, not for the firings. I think it might make a bit more sense if you have a look at the other workbook that I posted in post #7, as this will show you the entire worksheet layout and that the totals for the firings, trucking and remaing tonnes are all calculated at the bottom of the sheet (P423:X424)..

If you look in my "Depletions_Layout.xlsx" and the "Stope 1" worksheet you can see that the cell D4 you mentioned is situated in P19. The values in this cell are input manually by the end user, and reflect the values in the "Comments" section in column Y. The information in column Y is essentially showing the daily depletions the end user has done. For example, if you look in row 324, the value in cell S324 is the number of tonnes that was mined from the stope that day, in this case being 1,440 tonnes. On this day those 1,440 tonnes fully depleted firing 6, as by this stage there was only 237 tonnes remaining to be depleted from firing 6 (cell V225). So the user depleted those tonnes from firing 6 and then has to work backwards to deplete the remaining tonnes in the previous firings. In this case firing 5 had tonnes remaining to be depleted and so the user depleted the remainder of the 1,440 tonnes (1,203 tonnes) from firing 5. This matches the values noted in the "Comments" cell Y324 (237 t F6, 1203t F5). At which point the user must do a sumproduct calculation using the "Calculator" in T3:X13 to calculate the grade value to be assigned in cell T324 (7.1). In this case the user would enter 237 (tonnes depleted from firing 6) into cell W4, 6.48 (grade of firing 6) into cell X4, 1,203 (tonnes attributed to firing 5) into cell W5, and 7.2 (grade of firing 5) into cell X5. The sumproduct calcuator then returns the grade value to assign to that days tonnes, in this case 7.1, which then gets entered into cell T324.

The user would manually do the above process each day until all the firings have been depleted to 0, at which point the extra tonnes mined on that day are attributed to "overbreak".

The reason I would potentially prefer to have a formula based approach to solving this issue, rather than a macro, is that formulas will show the working and would allow at first glance to see on any particular day what firing the tonnes mined were attributed to and the grade assigned to it, which is extremely important for us to be able to do. If a macro could be used but still show the working for each day's depletions then that would be an equally desirable solution.

I apologise if I've not made this overly clear...I'm finding it rather hard to explain, as I personally find the way the creator of the sheet set it up to be incredibly confusing and rudimentary...hence my desire to automate it and tidy it up.

Regards,
Dan

[graphicgoose]

Just bumping this thread to see if anyone else may have any suggestions?

[xladept]

Hi Goose,

Thanks for the reminder - I'll give my regard to your issue tomorrow - no promises though since I didn't get it before

[graphicgoose]

Thanks xladept

[xladept]

Hi Goose,

Can you describe how the different column values are derived?

[graphicgoose]

Hi xladept,

I apologise in advance for this being a large post, but I feel the only way to properly explain how the sheet works to someone not familiar with dealing with this type of data on a day to day basis is to explain step by step how it would unfold over time. Please let me know if this makes sense.

If you're looking at my "Depletions_Layout.xlsx" spreadsheet from post #7 and looking at the "Stope1" worksheet, the values in columns O:Q are predetermined values assigned to each separate firing for the stope.

Column O are the firing names, the values in column P are the amount of tonnes of rock material planned to be fired with each firing (blown up with explosives), and the values in column Q are the Au grades of each firing (how many grams of gold per tonne of rock material). These values are calculated prior to being entered into this sheet. When a firing is taken the details for that firing are entered on the day it was fired (e.g. the RaiseBore was fired on the 1/01/1900. In this firing it was planned to blow up 193 tonnes of rock material at a grade of 5.49g/t, producing 34 ounces of gold).

Columns S:T relate to how much rock material is mined each day. For example, on the 1/01/1900 150 tonnes of rock material was mined (trucked) from this stope, this value therefore gets entered into S21. On this day the value in V21 (which is tracking how many tonnes are remaining to be mined from the Raisebore firing) would therefore have been 43 (P21-S21) as the 150 tonnes mined that day is less than the remaining tonnes planned for the RaiseBore firing (193 tonnes). Given all 150 tonnes of rock material mined on this day can be attributed to the RaiseBore firing (i.e. on this particular day the value in S21 is less than the value remaining in V21) the Au grade for the Raisebore firing (5.49g/t) is applied to all 150 tonnes of rock material and entered into cell T21. This therefore means 150 tonnes were mined on the 1/01/1900 at a grade of 5.49g/t to produce 26 ounces of gold.

Nothing more happened with this stope until the 18/01/1900, when Firing 1 was taken. Firing 1 was planned to be 979 tonnes at a grade of 0.94g/t of gold, producing 30 ounces of gold. These values are entered into O56:Q56, at which point the value in V56 would be 979 (references cell P56). During day shift (D/S) on the 19/01/1900 765 tonnes of rock material were mined from this stope, this value is therefore entered into cell S57. The value in V56 (remaining tonnes to be mined from firing 1) on this day would therefore have been 214 (P56-S57 as the 765 tonnes of material mined is less than the planned 979 tonnes). Again, given all the tonnes mined on the 19/01/1900 can be attributed to firing 1 the Au grade for firing 1 (0.94g/t) is applied to all 765 tonnes mined that day and entered into cell T57. This therefore means that on the 19/01/1900 765 tonnes of rock material were mined at a grade of 0.94g/t, producing 23 ounces of gold. At this point in time the value in cell V56 would be 214 and V21 would be 43.

On night shift (N/S) on 19/01/1900 firing 2 was taken. Firing 2 was planned to be 1,380 tonnes at a grade of 5.17g/t of gold, producing 229 ounces of gold. These values are entered into O58:Q58, at which point the value in V58 would be 1,380 (references cell P58). During day shift (D/S) on the 20/01/1900 1,125 tonnes of rock material were mined from this stope, this value is therefore entered into cell S59. The value in V58 (remaining tonnes to be mined from firing 2) on this day would therefore have been 255 (P58-S59 as the 1,125 tonnes of material mined is less than the planned 1,380 tonnes). Again, given all the tonnes mined on the 20/01/1900 can be attributed to firing 2 the Au grade for firing 2 (5.17g/t) is applied to all 1,125 tonnes mined that day and entered into cell T59. This therefore means that on the 20/01/1900 1,125 tonnes of rock material were mined at a grade of 5.17g/t, producing 187 ounces of gold. At this point in time the value in cell V58 would be 255, V56 would be 214 and V21 would be 43.

On day shift (D/S) on 29/01/1900 firing 3 was taken. Firing 3 was planned to be 10,764 tonnes at a grade of 9.46g/t of gold, producing 3,274 ounces of gold. These values are entered into O77:Q77, at which point the value in V77 would be 10,764 (references cell P77). During night shift (N/S) on the 29/01/1900 405 tonnes of rock material were mined from this stope, this value is therefore entered into cell S78. The value in V77 (remaining tonnes to be mined from firing 3) on this day would therefore have been 10,359 (P77-S78 as the 405 tonnes of material mined is less than the planned 10,359 tonnes). Again, given all the tonnes mined on the 29/01/1900 can be attributed to firing 3 the Au grade for firing 3 (9.46g/t) is applied to all 405 tonnes mined that day and entered into cell T78. This therefore means that on the 29/01/1900 405 tonnes of rock material were mined at a grade of 9.46g/t, producing 123 ounces of gold. At this point in time the value in cell V77 would be 10,359, V58 would be 255, V56 would be 214 and V21 would be 43.

This process continues the same until we get down to 31/05/1900, at this point we're onto firing 6.
During night shift (N/S) on the 30/05/1900 405 tonnes of rock material were mined from this stope, this value is therefore entered into cell S322. The value in V225 (remaining tonnes to be mined from firing 6) on this day would therefore have been 237 (P77- 43,965 as the 43,965 are the total tonnes mined from firing 6 between 12/04/1900 and 30/05/1900). Given all the tonnes mined on the 30/05/1900 can be attributed to firing 6 the Au grade for firing 6 (6.48g/t) is applied to all 405 tonnes mined that day and entered into cell T322. This therefore means that on the 30/05/1900 405 tonnes of rock material were mined at a grade of 6.48/t, producing 84 ounces of gold. At this point in time the value in cell V225 would be 237, V190 would be 2,577, V184 would be 113, V77 would be 1,584, V58 would be 255, V56 would be 214 and V21 would be 43.

Now this is where the process gets a little different.
During night shift (N/S) on the 31/05/1900 1,440 tonnes of rock material were mined from this stope, this value is therefore entered into cell S324. Given the value remaining in V225 from the previous day was 237, only 237 of the 1,440 tonnes mined on the 31/05/1900 can be attributed to firing 6. The value in V225 (remaining tonnes to be mined from firing 6) on this day would therefore have been 0 (P77- 44,202). This therefore leaves an extra 1,203 tonnes that need to be allocated to previous firings. Given firing 5 (cell V190) still has 2,577 tonnes remaining we can deplete the remaining 1,203 tonnes mined on the 31/05/1900. Given not all the tonnes mined on the 31/05/1900 can be attributed to firing 6 the Au grade for firing 6 (6.48g/t) is applied to 237 tonnes mined that day and the Au grade from firing 5 (7.2g/t) is applied to 1,203 tonnes mined that day. Using the "Calculator" in cells T3:X13 the weighted grade for the 1,440 tonnes mined on the 31/05/1900 is 7.1g/t which is then entered into cell T324. This therefore means that on the 31/05/1900 1,440 tonnes of rock material were mined at a grade of 7.1/t, producing 329 ounces of gold. At this point in time the value in cell V225 would be 0, V190 would be 1,374, V184 would be 113, V77 would be 1,584, V58 would be 255, V56 would be 214 and V21 would be 43.

This process continues until all values in column V (remaining tonnes from previous firings) are depleted to 0, at which point any extra tonnes mined are attributed to Overbreak, as this is classified as extra rock material that has been mined beyond the planned number of tonnes fired. This can be seen if we look at the 21/06/1900, at which point we've moved on to firing 7,

During day shift (D/S) on the 21/06/1900 1,485 tonnes of rock material were mined from this stope, this value is therefore entered into cell S365. Given the value remaining in V352 from the previous day was 1, only 1 of the 1,485 tonnes mined on the 21/06/1900 can be attributed to firing 7. This therefore leaves an extra 1,484 tonnes that need to be allocated to previous firings. Given at this point in time the values in V352 would be 1, V225 would be 0, V190 would be 0, V184 would be 0, V77 would be 0, V58 would be 0, V56 would be 120 and V21 would be 43 we deplete 1 tonne from V352 (firing 7), 120 tonnes from V56 (firing 1), 43 tonnes from V21 (RaiseBore) and the remaining 1,321 tonnes of the 1,485 tonnes mined on 21/06/1900 are allocated to Overbreak.
Given not all the tonnes mined on the 21/06/1900 can be attributed to firing 7 the Au grade for firing 7 (11.05g/t) is applied to 1 tonne mined that day, the Au grade from firing 1 (0.94g/t) is applied to 120 tonnes mined that day, the Au grade from RaiseBore (5.49g/t) is applied to 43 tonnes mined that day and a grade of 0.5g/t (grade of waste assigned to overbreak) is applied to the remaining 1,321 tonnes mined that day. Using the "Calculator" in cells T3:X13 the weighted grade for the 1,485 tonnes mined on the 21/06/1900 is 0.69g/t which is then entered into cell T365. This therefore means that on the 21/06/1900 1,485 tonnes of rock material were mined at a grade of 0.69/t, producing 33 ounces of gold. At this point in time the value in cell V352 would be 0, V225 would be 0, V190 would be 0, V184 would be 0, V77 would be 0, V58 would be 0, V56 would be 0, V21 would be 0 and the value of P19 (overbreak) would be 1,321.

Regards,
Dan

[xladept]

Hi Dan,

You've described the layout entries and values but we were working on your new test sheet in the other book

I need to know the difference between Overbreak and Working Overbreak, Depletion and Working Depletion.


Orrin


P.S. Here:Depletions_Test.xlsm I think I may have them backwards - please correct.