Forum Discussion

shahzad_afzal's avatar
shahzad_afzal
Copper Contributor
Aug 31, 2022

Date Range

Hi

I have data of every sales guy in the team of their daily sales across the year with dates...

I need to dynamically extract start data and number of days every sales guy is taking to earn $2000 in total... This will help me understand product vs. season of product vs. region of sales of the sales guy...

 

Our target to earn $2000 is min 120 days and maximum 160 days...

 

How to extract suitable time window in excel please...?

excel
  • shahzad_afzal's avatar
    shahzad_afzal
    Copper Contributor
    Sep 07, 2022
    Hi.
    Now 120 days process is working perfectly fine and thanks for supporting with quite effortless process. 120 - 160 days mean; we have min 120 days to achieve target and max 160 days. After 160 days, the sales guy didn't qualify for the sales incentive. Is this possible to have min and max days formula to count for $2000?
    • dscheikey's avatar
      dscheikey
      Bronze Contributor
      Sep 07, 2022

      shahzad_afzal 

      I come back to my first formula, which already did exactly what you want. Minimum and maximum do not work at all. You can only count how many days it takes to reach the 2000$. That is the result of minimum and maximum.

      I have made an example again. There it should possibly become clearer.

       

  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Aug 31, 2022

    Hishahzad_afzal,

    I have created a sample file for you in which your task (if I have understood it correctly) is solved. It calculates for each date how many days it takes to reach $2000.

    Get back to me if I didn't understand it correctly.

    For the function I used a formula which is not yet available in every Excel version. In case of need you can try it in Excel for the web.

    There are probably still simpler possibilities. Let's see if someone else has a better idea.

    =IFERROR(XLOOKUP(2000,BYROW(ROW(2:161),LAMBDA(in,SUMIF(M2:M161,"<="&in,B2:B161))),A2:A161,,1)-A2+1,NA())

     

      • dscheikey's avatar
        dscheikey
        Bronze Contributor
        Sep 01, 2022

        shahzad_afzalUnfortunately, it is still very difficult for me to understand what you actually want to do. Your explanations are contradictory and not comprehensible for me. I don't understand if you are calculating time periods or sums at fixed time periods. When do the 160 days come into play. Your example only makes a calculation with 120 days. Strictly speaking 121 days. I have attached another example. Possibly we come further with it.

Resources