Calculating total for multiple overlapping values

[tonyjackson]

I need to find a way to calculate a total value of several (up to 10) separate entities, where the entities can overlap with each other by a given percentage.

So imagine a grid 10x10 with an overlap percentage in each cell defining how much that entity overlaps with another. Each one has a defined value as well of course.

At one level where all entities completely overlap with each other (imagine a venn diagram with 10 circles on the same origin) then the total is just the value if the largest one. At the other end, where the overlaps are zero, then the total is simply the sum of them all.

The difficulty is how to compute the value of all the various overlaps, which seems to be scarily complicated as you add entities. I don't remember this from my collage maths days and it may be something that's not possible. However, can anyone suggest whether this can be done in Excel?

Thanks

Tony

[Saarang84]

Hi Tony (Stark I assume ),

Welcome to the Forum. Can you give us a workbook with sample data and briefly explain your need with a example?

[tonyjackson]

Yes, Ironman here!

I've attached a file with some simple commentary. I hope it makes sense.

I've populated it with data for 3 areas and you will see that I forced it to calculate the correct total in a completely false way. Of course this cannot be a general solution.

If I can do more to explain, please let me know

Thanks again

Tony