Formula to spread numbers based on weekend vs weekday

[usnapoleon]

Backstory
Our company currently spreads budgeted revenue evenly through a month, regardless if its a weekday or weekend or holiday. Business picks up on the weekend so we want to report higher budgeted numbers on the weekends.

Example data
This is the lumpsum amount of rooms booked for the whole month

	E	F
January
Internet Booking (.com billing)

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="bgcolor: #969696, align: right"]Month[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #969696, align: right"]Total Days[/TD]
[TD="bgcolor: #CCFFFF, align: center"]31 [/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #000000"]RENTAL BUDGETED REVENUE[/TD]
[TD="bgcolor: #000000, align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #BFBFBF"]Number of Rooms Sold[/TD]
[TD="bgcolor: #000000, align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="bgcolor: #FFFF00, align: right"] 246 [/TD]

</tbody>

Budget​

I have this data for January

	S	T	U	V
Budget Year	Weight
Weekday
Weekend
Month	Weekdays	Weekends	Variance to row3 calendar
January

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFF00, align: center"]2017 [/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FFFF00, align: right"]40%[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FFFF00, align: right"]60%[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="bgcolor: #FFFF00"] 22 [/TD]
[TD="bgcolor: #FFFF00"] 9 [/TD]
[TD="align: right"] - [/TD]

</tbody>

Budget​

The amount of 246 is going to be weighted based on if it's a weekday or weekend, and then spread among the quantity of weekdays or weekend days. So let's look at a Monday for example. Weekdays will receive a weight of 40% of the 246, and then that value will be spread across the 22 weekdays of the month. A Monday, or any weekday really, will have the value of 4.47

I have this area here to populate with this information:

	A	B	C	D	E	F	G
Internet Booking (.com billing)

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Sunday[/TD]
[TD="align: center"]Monday[/TD]
[TD="align: center"]Tuesday[/TD]
[TD="align: center"]Wednesday[/TD]
[TD="align: center"]Thursday[/TD]
[TD="align: center"]Friday[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]DATE[/TD]
[TD="bgcolor: #FFFF00, align: center"]1/1/17[/TD]
[TD="align: center"]1/2/17[/TD]
[TD="align: center"]1/3/17[/TD]
[TD="align: center"]1/4/17[/TD]
[TD="align: center"]1/5/17[/TD]
[TD="align: center"]1/6/17[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #7030A0"]Statistics[/TD]
[TD="bgcolor: #7030A0, align: right"][/TD]
[TD="bgcolor: #7030A0, align: right"][/TD]
[TD="bgcolor: #7030A0, align: right"][/TD]
[TD="bgcolor: #7030A0, align: right"][/TD]
[TD="bgcolor: #7030A0, align: right"][/TD]
[TD="bgcolor: #7030A0, align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #5B9BD5, align: center"]Lawrence Welk Resort Villas (A1)[/TD]
[TD="bgcolor: #5B9BD5, align: center"][/TD]
[TD="bgcolor: #5B9BD5, align: center"][/TD]
[TD="bgcolor: #5B9BD5, align: center"][/TD]
[TD="bgcolor: #5B9BD5, align: center"][/TD]
[TD="bgcolor: #5B9BD5, align: center"][/TD]
[TD="bgcolor: #5B9BD5, align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #BFBFBF"]Number of Rooms Sold[/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]

[TD="align: center"]10[/TD]

</tbody>

Budget Rooms​

So I need that 4.47 to reflect along the line for Monday-Friday... but I need all of this to be done via formulas.

I'm racking my brain, but it refuses to work and I cannot bribe it currently

Please help

 

[usnapoleon]

Also to keep in mind, the area i need to put a formula in extends the whole year, 366 cells across. So if it's January it will use the January in the top image F15, if it's February it will use the February amount in (not included in the image) G15.

Same thing for the 2nd image, if it's January and a weekday it will use T8, but if its February it will use T9, etc.

I'm not quite sure how to keep it simple. On the 'Budget Rooms' tab, from the bottom image, I want it to determine what it's looking for, like defining itself "hey, this is Monday and March, so I need to reference the 'Budget' tab to get the total and break that total down based on the weight and number of weekdays in March"

Does this make sense, I hope so! Again, thank anyone in advance!!!