Sep 15 2020 06:18 PM
Hi
I have daily sales qty in the past year.
I need to calculate
a) what is the minimum sum of sales in the last year in any given day's range. For e.g. what is the minimum sum of sales in a 10 day consecutive days bucket from the last year's daily sales? The 10 consecutive days could be anywhere in the last 365 days but that 10 days sum must be the least of any 10 days consecutive sum. It could be from Jun1st to Jun 10th or from Jun 2nd to Jun 11th or Aug 10th to Aug20th. What is the least 10 consecutive days sum from my last year's data?
b) what is the maximum sum of sales in the last year in the given day's range. For e.g. what is the maximum sum of sales in a 15 day consecutive days bucket from the last year's daily sales? The 15 consecutive days could be anywhere in the last year but that 15 day sum must be the maximum of any 15 days consecutive sum. It could be from Jun1st to Jun 15th or from Jun 2nd to Jun 16th or Aug 10th to Aug25th. What is the max 15 consecutive day's sum from my last year's data?
Thank you very much for your great help.
Sep 15 2020 10:21 PM
Solution
I attached that I believe you can use as an example. You should be able to adjust the range references as needed.
Also, the formulas are array formulas and generally require you to hit Ctrl+Shift+Enter. But, the latest version of Excel may not require it, so if you hit Enter and get an error, go back into the formula bar and hit Ctrl+Shift+Enter.
Sep 16 2020 08:32 AM
Brilliant solution. Yes, it works. Thank you very much for your great help. Truly appreciate the quick response.
Sep 16 2020 08:46 AM
Sep 15 2020 10:21 PM
Solution
I attached that I believe you can use as an example. You should be able to adjust the range references as needed.
Also, the formulas are array formulas and generally require you to hit Ctrl+Shift+Enter. But, the latest version of Excel may not require it, so if you hit Enter and get an error, go back into the formula bar and hit Ctrl+Shift+Enter.