Aug 31 2022 02:04 AM - edited Aug 31 2022 02:06 AM
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...?
Aug 31 2022 01:06 PM
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())
Sep 01 2022 03:26 AM
@dscheikey Thanks for your response. I am attaching a file for your review as the formula in your above message is complicated. I have added an explanation too for your review.
Sep 01 2022 10:56 AM
@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.
Sep 01 2022 11:45 AM
Sep 01 2022 01:53 PM
Sep 07 2022 09:50 AM
Sep 07 2022 11:53 AM
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.