SOLVED

Calculate max sales volume in a given range of data from the last year daily data

%3CLINGO-SUB%20id%3D%22lingo-sub-1671332%22%20slang%3D%22en-US%22%3ECalculate%20max%20sales%20volume%20in%20a%20given%20range%20of%20data%20from%20the%20last%20year%20daily%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1671332%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20have%20daily%20sales%20qty%20in%20the%20past%20year.%3C%2FP%3E%3CP%3EI%20need%20to%20calculate%3C%2FP%3E%3CP%3Ea)%20what%20is%20the%20minimum%20sum%20of%20sales%20in%20the%20last%20year%20in%20any%20given%20day's%20range.%20For%20e.g.%20what%20is%20the%20minimum%20sum%20of%20sales%20in%20a%2010%20day%20consecutive%20days%20bucket%20from%20the%20last%20year's%20daily%20sales%3F%20The%2010%20consecutive%20days%20could%20be%20anywhere%20in%20the%20last%20365%20days%20but%20that%2010%20days%20sum%20must%20be%20the%20least%20of%20any%2010%20days%20consecutive%20sum.%20It%20could%20be%20from%20Jun1st%20to%20Jun%2010th%20or%20from%20Jun%202nd%20to%20Jun%2011th%20or%20Aug%2010th%20to%20Aug20th.%20What%20is%20the%20least%2010%20consecutive%20days%20sum%20from%20my%20last%20year's%20data%3F%3C%2FP%3E%3CP%3Eb)%26nbsp%3Bwhat%20is%20the%20maximum%20sum%20of%20sales%20in%20the%20last%20year%20in%20the%20given%20day's%20range.%20For%20e.g.%20what%20is%20the%20maximum%20sum%20of%20sales%20in%20a%2015%20day%20consecutive%20days%20bucket%20from%20the%20last%20year's%20daily%20sales%3F%20The%2015%20consecutive%20days%20could%20be%20anywhere%20in%20the%20last%20year%20but%20that%2015%20day%20sum%20must%20be%20the%20maximum%20of%20any%2015%20days%20consecutive%20sum.%20It%20could%20be%20from%20Jun1st%20to%20Jun%2015th%20or%20from%20Jun%202nd%20to%20Jun%2016th%20or%20Aug%2010th%20to%20Aug25th.%20What%20is%20the%20max%2015%20consecutive%20day's%20sum%20from%20my%20last%20year's%20data%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20your%20great%20help.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1671332%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1671737%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20max%20sales%20volume%20in%20a%20given%20range%20of%20data%20from%20the%20last%20year%20daily%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1671737%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F795024%22%20target%3D%22_blank%22%3E%40prasad1211%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attached%20that%20I%20believe%20you%20can%20use%20as%20an%20example.%20You%20should%20be%20able%20to%20adjust%20the%20range%20references%20as%20needed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20the%20formulas%20are%20array%20formulas%20and%20generally%20require%20you%20to%20hit%20Ctrl%2BShift%2BEnter.%20But%2C%20the%20latest%20version%20of%20Excel%20may%20not%20require%20it%2C%20so%20if%20you%20hit%20Enter%20and%20get%20an%20error%2C%20go%20back%20into%20the%20formula%20bar%20and%20hit%20Ctrl%2BShift%2BEnter.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1673859%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20max%20sales%20volume%20in%20a%20given%20range%20of%20data%20from%20the%20last%20year%20daily%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1673859%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBrilliant%20solution.%20Yes%2C%20it%20works.%20Thank%20you%20very%20much%20for%20your%20great%20help.%20Truly%20appreciate%20the%20quick%20response.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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. 

 

3 Replies
Highlighted
Best Response confirmed by prasad1211 (Occasional Contributor)
Solution

@prasad1211 

 

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.

Highlighted

@JMB17 

 

Brilliant solution. Yes, it works. Thank you very much for your great help. Truly appreciate the quick response. 

 

 

Highlighted