Depleting from multiple values at once

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Bump

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

Another friendly bump

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.

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!

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

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

Thanks xladept

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