Help!?! Weighted numbers greater than whole

[Sleepwa]

Hi

Been reading through the forums and haven't found anything to help my issue.

I have a set number that I need to allocate over a flexible period of say 5 -11 days.
I have applied the traditional split per day to each number i.e. a monday normally results in 20% of transactions, Tuesday 25% etc.
The issue comes in when I try to allocate the budget over a period longer or shorter than a week.

Therefore the budget for the 9 days is being allocated over the period of 9 days which results in a overall allocation of 150%. (duplicate mondays etc).
Is there a way to limit the entire period to a set number and have the numbers adjust themselves accordingly up or down to match it other than multiplying it by a factor?

Example attached:

Excel Problem.xlsx

Thanks for the help!

Sleepwa

[aydeegee]

Hi Sleepwa

Welcome to the forum.

The problem exists because you will insist on using the old fashioned dating system. When you get to use the deci-dating system, it will become much easier! (10 days to a week, 10 month to a year. Simple!

In the meantime, I guess you will just have to get used to the fact that there are not 4 weeks in a month (don't you just love February?) - hence the use of factors.

A system that I have used is as attached: base the system on a 4 week month, put whatever variations on the weekday % (but make sure that the total 100% . Spread these over the month, and then reduce the % so that they total 400%

It will be interesting to see what other interesting solutions are suggested.

Regards
Alastair

[Sleepwa]

Hi Alastair

Thanks for the reply.

Very clever solution. Thanks.

I unfortunately have to spread the month into 4 segments as per forecast requirements but this should help alot!
Let me go play

Regards,

Darren

[daddylonglegs]

Try this formula in C11 copied across

=INDEX($C$4:$F$4,C$7)*$B$4*INDEX($K$4:$Q$4,MATCH(TEXT(C9,"ddd;;"),$K$2:$Q$2,0))/SUMPRODUCT(($C$7:$AG$7=C7)+0,SUMIF($K$2:$Q$2,TEXT($C$9:$AG$9,"ddd;;"),$K$4:$Q$4))

Now the whole month will add up to the figure in B4, with the correct split across the weeks - this should work whatever percentages you have in C4:F4 and K4:Q4 (as long as both of those ranges total to 100%) and however you split the weeks - see attached. Yellow cells aren't required for the solution - that simply verifies that the sums are correct for each week