Time overlap %

Hi there,

Since I'm new to this forum, a short introduction seems to be in place: I'm Stefan, living in The Netherlands, working with Excel on a regular basis, but nothing fancy so far...
I've encountered a problem which is illustrated in the uploaded example.
This is the (example)case:
Lets assume that person A makes a living out of talking. He talks all day long. Sometimes with only 1 person, sometimes with multiple persons, sometimes with non.
Lets also assume that this person costs €0,3/minute. If he talks face-to-face with only one person, that person has to pay him €0,3/minute. But as soon as a secon person joins the conversation, both have to pay him 50% of €0,3/minute. If a third person joins, they all have to pay an equal share of 33,33%, and so one. From the moment one person leaves the conversation, the pay-rate changes.
For further illustration, see example file.

Limitations: In the example file, everything is processed in blocks of 10 minutes. In the real file, this is not the case. Also, in the example file all dates are nicely sorted, in the real file, this is not the case as well. Last but not least: I only have the start and end date and time of the persons talking te person A. The available information is marked in yellow.

The big question: Is there af formula that calculates the cost per person?

Time overlap calculation.xlsx


Should you have any questions or is something not clear, please let me know!
Kind regards from The Netherlands,
Stefan

I think you are better off if you add your data verticaly.

Use a helpcolumn to determine if another person is add to that conversation.

In a second helpcolumn you can add (or make a formula) to add the %.

After that you should be able to count the cost.

P.s. you used merged cells in your file.

My advice, don't use merge cells, you get in trouble with it sooner or later.