Using Randbetween to Project Annual Sales on Monthly Basis

Hi,

I am in need of help using Randbetween (or any other method) to randomly project annual sales on a monthly basis. Example: A total of 20 sales are projected in 2013 that I need to randomly spread by month with a monthly minimum of 0 and maximum of 2. Duplicates are okay but the total has to equal 20.

Any ideas would be greatly appreciated. Thanks in advance.

In cells a1:a12 type January to December
in cells b1:b12 copy the formula = randbetween(0,2)
in cell b14 type the formula = SUM(B1:B12)
run this macro. It will recalculate the sheet until the Sum in B14 equals 20

Please Login or Register  to view this content.

Perfet, your solution worked like a charm. You have saved me many hours of work trying to figure this out. Many thanks.

The solution provided in response to my initial request works great for one product line projection but now that I have used it in my full blown scenario with over 20 product lines I have run into a problem. My data is set up in a table with over 20 rows and I am only able to get the random projections from one product line in the table to equal the corresponding annual total. I think this is because of the automatic recalculation associated with the Randbetween function.

New example: I need to randomly spread varying annual sales figure by product line by month with one macro.

Any help with the code to accomplish this would be greatly appreciated. Thanks.

if each item has a separate target, simply repeat the process above for different each product, and copy the macro but adjust the target cell eg B14, c14, D14

I have copied the macro for each distinct product line and it works fine on an individual basis but I don't understand how to run them together so each equals their individual target after the macro has run. Due to the volatility of the Randbetween function and the constant recalculation that occurs only the last prouduct line written in the macro equals the target. All of the previous lines recalculate to a random total. I am very inexperienced with Visual Basic so it's certainly possible I'm missing something very basic but it seems something additional needs to be written into the code that "freezes" each line once the target is reached.

Thanks for your help and patience.

Hi!
I am pretty much trying to acheive the same results but I need to add one level of complexity, the countries. Basically, I need to generate random sales, for n products, for n countries, by month knowing that the total sales by month needs to constrained by a fixed number.

For example, generate random sales for 10 products and 5 countries for one month, but the total of sales cannot be higher than 1000, and sales are between 0 and 200. Is this something feasible ?

Thanks !

Hello Tonyglandyl, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.