I've been across most of the internet to find an answer to my quandary. I'm hoping you fine folks can help me out!
I've created a retail sales goal tracker that sets daily sales targets based on historical sales percentages. My formula in the 'daily target' column takes the monthly $ target, divides that number by the number of sales days and then determines each sales day's target $ depending on what day of the week it is and what that weekday's historical average percent of weekly sales is (i.e. 15.04% on Mondays, 14.63% Tuesday, etc.) My problem is that the daily sales target $ don't add up to the full monthly target because of the weekday composition within a month. So M-Sa percentages total 100%, but a month that ends on a Tuesday is coming up short by 3 days. How can I alter my formula so that it accounts for the uneven weekday composition within a month and the sum of the individual sales targets equals the monthly sales target?
For reference, here's the formula as it is now: =SUM((($C$4/25)*6)*(IF(WEEKDAY(E6)=2,$C$19,IF(WEEKDAY(E6)=3,$C$20,IF(WEEKDAY(E6)=4,$C$21,IF(WEEKDAY(E6)=5,$C$22,IF(WEEKDAY(E6)=6,$C$23,IF(WEEKDAY(E6)=7, $C$24,0))))))))
$C$4 is the monthly sales target and $C$19:$C$24 are each day's percentages to the weekly goal.