# distribute monthly sales to days

Copper Contributor

# distribute monthly sales to days

I have a total monthly sales projection . i want to distribute it randomly into daily , keeping in mind holidays !

3 Replies

# Re: distribute monthly sales to days

I have a total monthly sales projection . i want to distribute it randomly into daily , keeping in mind holidays !

Two sentences.

You know what you mean.

But it's ambiguous to somebody who's not you and needs more precision in order to write formulas or functions. What exactly, for one, does "keeping in mind holidays" mean? One could assume it means "don't include them" but which ones, and does it in fact mean that in the first place? Many sales of many products HAPPEN during holidays. Maybe double the normal day. And so forth.

If you are expecting to write a formula (or set of formulas) you need to be a lot more specific, more precise.

And just out of curiosity, is this a school homework assignment?

# Re: distribute monthly sales to days

example..

I have total sales for January 235000, I want to distribute them randomly on days of same month while Thursdays and Fridays ( weekends)are usually 30% to 40 % more than other days ( week days) ..

# Re: distribute monthly sales to days

Your first post: I have a total monthly sales projection . i want to distribute it randomly into daily , keeping in mind holidays !

Now it's: I have total sales for January 235000, I want to distribute them randomly on days of same month while Thursdays and Fridays ( weekends)are usually 30% to 40 % more than other days ( week days) ..

No mention of holidays (Thursday and Friday are weekly and routine weekends, not holidays) and you use the word "usually" which means plain old random would probably work for all days since "usually" might as well be random. A truly clear definition of the problem, or the situation, the desired result, is needed.

In the absence of that,  I would take the days of a month (28, 29, 30, or 31) and generate for each day a random number using the RAND function. Add up all those random numbers to get a sum (let's call it RandSum). Then a simple formula taking your desired monthly total and multiplying it by each day's random number divided by RandSum gives you random sales figures for each of the 30 days. And they add up to the desire total. The formula is

=(A1/RandSum)*MthlyTotal

Here's what the first few rows look like. You can change the figure in yellow to see how it distributes for different amounts.

I've attached a sheet that does this--ignoring weekends and holidays. I'll leave those refinements, if they really matter, to you to articulate and then create the modified formula. As noted above, it's not clear to me--since we're talking random numbers anyway, for every day--why it should matter.

Here's a description of the RAND function.