Forum Discussion
Date Range
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 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.
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.
- Dear
Your sheet gave me quite a straightforward way to count a continuous 120 rows to check the total sum equaling 2,000. I can find the nearest date where the difference is not more than 2 or 3 so I can consider it as my start date. If I want to have a range, example; the sum of 121 days comes to 1990 and sum of 126 days comes to 2000. How to adjust this variation? I need answer as 126.- Please prepare your question with a new document or a screenshot. I absolutely do not understand what the question should be. Which variation? Where?