Forum Discussion

3 Replies

  • mathetes's avatar
    mathetes
    Gold Contributor
    Dec 16, 2023

    Sherief_Sobh 

     

    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? 

    • Sherief_Sobh's avatar
      Sherief_Sobh
      Copper Contributor
      Dec 16, 2023

      mathetes 

      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) ..

       

       

      • mathetes's avatar
        mathetes
        Gold Contributor
        Dec 18, 2023

        Sherief_Sobh 

         

        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.