Splitting Costs

Kind of an unusual math/excel problem, at least for me.

A bachelor party is being planned. A house was rented for 2 days for $1,800.

There are 11 guests total. But not all will be staying both nights, some will only be staying one night. I'm trying to put together a table that will show the even costs split for each possible scenario of guests staying 1 night vs guest staying 2 nights. Something like the attached image. Everyone staying 1 night and everyone staying 2 nights is pretty simple. But what about when 5 guests stay 1 night and 6 guests stay 2 nights?

Bachelor party.jpg

TIA!

Originally Posted by FDibbins

Sum all nights stayed for all people. Then divide each persons nights by that as the total

Thanks FDibbins. I was staring at it and thought I may have overestimated the complexity. This look about right?

Attachment 624447