Weighted month budget listed per day

[p3b0_]

Hello!


I am trying to do a budget with the final result to be listed per day like so:
[TABLE="width: 300"]
<tbody>[TR]
[TD]01-jan[/TD]
[TD]sunday[/TD]
[TD]$xxx[/TD]
[/TR]
[TR]
[TD]02-jan[/TD]
[TD]monday[/TD]
[TD]$xxx[/TD]
[/TR]
[TR]
[TD]03-jan[/TD]
[TD]tuesday[/TD]
[TD]$xxx[/TD]
[/TR]
</tbody>[/TABLE]
and so on up to the last of december.


January budget: $100 000 would give about $3226 per day in average... but, my problem is that I also would like to have different weight depending on the day of the week.


Let's say every week in january have this weight:
Monday: 6%
Tuesday: 7%
Wednesday: 8%
Thursday: 9%
Friday: 25%
Saturday: 28%
Sunday: 17%


I'm having trubble since every month have different amount of weekdays. For example january 2017 have 5 sun-tue and 4 wed-sat. I can't seem to wrap my head around this problem and would appreciate any kind of help! My problem is how to calculate it, and it would be more then enough to explain how to make january work with the above data.

Please let me know if you need any more information, or if something needs to be explained.

Thank you,

Peter

 

[jorismoerings]

Hi and welcome to MrExcel,

Table below creates based on the month the number of days and recalculates your weightage to a monthly weight and hence a value:

Excel 2016 (Windows) 64 bit
	A	B	C	D
1	Month	1-2-2017
2	Amount	100000
3
4	Weekday	Weight	# In month	Amount per day
5	Monday:	6%	4	€ 1,500,00
6	Tuesday:	7%	4	€ 1,750,00
7	Wednesday:	8%	4	€ 2,000,00
8	Thursday:	9%	4	€ 2,250,00
9	Friday:	25%	4	€ 6,250,00
10	Saturday:	28%	4	€ 7,000,00
11	Sunday:	17%	4	€ 4,250,00
Sheet1

Cell Formulas
Range	Formula
C5	=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($B$1&":"&EOMONTH($B$1,0))))=2))
C6	=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($B$1&":"&EOMONTH($B$1,0))))=3))
C7	=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($B$1&":"&EOMONTH($B$1,0))))=4))
C8	=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($B$1&":"&EOMONTH($B$1,0))))=5))
C9	=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($B$1&":"&EOMONTH($B$1,0))))=6))
C10	=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($B$1&":"&EOMONTH($B$1,0))))=7))
C11	=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($B$1&":"&EOMONTH($B$1,0))))=1))
D5	=B5/SUMPRODUCT($B$5:$B$11,$C$5:$C$11)*$B$2

Hope this helps.

 

[p3b0_]

Pure genius, that was exactly what I needed! Five stars!

Thank you very much!