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:

maximum easting | minimum easting | max northing | min northing | max depth | min depth | density |

>>>> 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!!