Grid blocks

Hi there,

Im working on an unusual project for Excel, but I think it is possible to come up with a solution. I hope someone can help me with the following situation.

It gives 2 main databases;

sheet A> A 2x2 km area is divided into multiple grid blocks. Every block is 50x50m (and infinitive height), so it gives 40x40=1600 blocks in this area. Currently it just gives two columns with all the easting / northing lines (eg. 0, 50, 100, 150, ..., 2000) but this can be changed easily to achieve the aim of this task.

sheet B> In this 2x2 km area there are many (>4000) blocks; these blocks are having all different dimensions (length: 5 - 150 m, width: 5 - 150 m depth: 5 - 150 m) and are scattered randomly over the total area. For these blocks the following data are available:

>>>> What I now what to do, is to combine these data to get for every 50x50m grid block the weighted averaged density. Herefore I need to know which blocks are contributing to these grid blocks and in which ratio they contribute.

Many thanks for any advice on how to solve this problem!!

Do you have a sample excel file?
I'm really interested to know how this will work.

Sure, I attached a part of the excel file to this reply. Sheet 1 contains the (production) blocks incl. max /min of easting, northing and depth, material type, tonnage, volume and density. Sheet 2 is a start up of the grid blocks. You can adjust this to whatever you want, to fit the data into it.

In addition, the easting and northing is shown not from 0 - 2000m, but in a different format: easting: 32000 - 34000, northing 59000 - 61000. All the blocks with values outside these ranges can be discarded.

View attachment PRODUCTION DATA - BLOCKS - excelforum.xlsx
Hi..
Please see attached.
Please copy the green cells in first 2 sheets and fill down up to last row.

What I've done:
1. Divided the easting and northing into 40 grids and calculated how many meters of the grid was occupied by each block. Example, block 1 occupies 38 meters of grid 3 (easting), 50 meters from grid 4 to 31 and 16 in grid 32. And for the northing, same method.
2. Added a drop down so you can select which block you want to see.
3. Calculated the tonnage and volume of the materials located on the selected block.
Calculation of tonnage: Total tonnage (column B)*((easting in particular grid*northing)/(total easting*total northing)
What I understand:
a. The tonnage (column B) is the total weight of the material in the location denoted by the easting, northing and depth.
b. Menge (column C) is the total volume weight of the material in the location denoted by the easting, northing and depth.
Example (block 1): There is a total of 4750 tons of material 740 with total volume of 2638 cubic meters scattered in the area denoted by the easting, northing and depth (total volume of area is 1455x1301x120).
4. Calculated the weighted density of all materials located in the specified grid.


Waiver:
Please don't think that my solution is 100% correct. I have no idea what this is all about and I just made this up based on what I can understand.

I have erased the formula on the subsequent rows because I could not upload the file with that. There is a limit in file size of the upload.

View attachment PRODUCTION DATA - BLOCKS - excelforum (1).xlsx

peter.abing said:

I have erased the formula on the subsequent rows because I could not upload the file with that. There is a limit in file size of the upload.

Click to expand...

I have messed up somewhere. The drop down where you can choose which block you want to see is messed.
So I now attach the corrected file.

This will only allow you to see per block. Then you can just filter in the second sheet to see which blocks contributed to the total tonnage and volume.

Please tell me what you think. I am now in the process of creating a sheet where you can see the tonnage, volume and density of each block.

I'm not an expert. This is a hobby. Learning Excel by doing challenging tasks.

I think there is a more efficient way of doing this.