Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-01-2017, 07:05 AM
mreynolds1775 mreynolds1775 is offline laregest 30 consecutive average of values in a 180 cell range Windows 10 laregest 30 consecutive average of values in a 180 cell range Office 2013
Novice
laregest 30 consecutive average of values in a 180 cell range
 
Join Date: Sep 2017
Posts: 12
mreynolds1775 is on a distinguished road
Default laregest 30 consecutive average of values in a 180 cell range

Good morning all,

I am looking to find the highest 30 day average of sales within a 180 range. I already track total sales by month, but as an example, this doesn't work as an accurate depiction. Example: first half of Aug sales were low, second half were high; first half of September sales were high, and the second were low. The period from mid august to mid September was the highest sales period.

any clue how to find this info? thanks in advance.

-Mike
Reply With Quote
  #2  
Old 10-01-2017, 07:55 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline laregest 30 consecutive average of values in a 180 cell range Windows 7 64bit laregest 30 consecutive average of values in a 180 cell range Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default

Please post a sample sheet with some data and expected results - Thx
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 10-01-2017, 09:10 AM
mreynolds1775 mreynolds1775 is offline laregest 30 consecutive average of values in a 180 cell range Windows 10 laregest 30 consecutive average of values in a 180 cell range Office 2013
Novice
laregest 30 consecutive average of values in a 180 cell range
 
Join Date: Sep 2017
Posts: 12
mreynolds1775 is on a distinguished road
Default

Peco,

Thanks. Please see attached example. Highlighted cell represents the desired result. I want this to be an automated so that I can determine worst case scenario for demand of an item.

Initially, I had thought that using =average(Large(range{1,2,3,4,5,...30})) would do the trick, but that will give me the average of the 30 highest days of sales, not the highest 30 days of sales....make sense?

-Mike
Attached Files
File Type: xlsx high average sales over large range.xlsx (11.3 KB, 12 views)
Reply With Quote
  #4  
Old 10-01-2017, 11:15 AM
ArviLaanemets ArviLaanemets is offline laregest 30 consecutive average of values in a 180 cell range Windows 8 laregest 30 consecutive average of values in a 180 cell range Office 2016
Expert
 
Join Date: May 2017
Posts: 932
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
Default

I can see only 2 possible solutions:
1. A VBA script which reads 180 days data into array, and runs a 150-step cycle, calculates an average (there is a shortcut when you save before the first value in cycle, so you can deduce it in next cycle and add a new last value instead) for every step, and when this is more than max average until this step, saves the new max value into variable(s) or another array (probably you need period start and end dates too), or into predefined Name;
2. A separate table with columns PeriodStart and PeriodEnd which are calculated for first row as (TODAY() - 180 +ROW() - X) AND (TODAY() - 180 + ROW() - X) + 30, where X is the number of row for 1st period. For every next row, the period shifts 1 day, and you'll have 150 rows of data in this table. In 3rd column, average for period is calculated from your data table (Using SUMIFS() formulas). And then you can calculate max average using MAX() function on column with averages (calculate it as Name - then it will be much easier to use it in formulas).
Reply With Quote
  #5  
Old 10-01-2017, 10:56 PM
xor xor is offline laregest 30 consecutive average of values in a 180 cell range Windows 10 laregest 30 consecutive average of values in a 180 cell range Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,100
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

I am not sure, but you can try to take a look at row 14 in the attached file.

Cell B14 shows the average sales for first 30 days, cell C14 shows average sales for 02-jan-17 to 31-jan-17 and so on.
Attached Files
File Type: xlsx high average sales over large range_2.xlsx (16.5 KB, 11 views)
Reply With Quote
  #6  
Old 10-01-2017, 11:09 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline laregest 30 consecutive average of values in a 180 cell range Windows 7 64bit laregest 30 consecutive average of values in a 180 cell range Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default

Quote:
Originally Posted by mreynolds1775 View Post
but that will give me the average of the 30 highest days of sales, not the highest 30 days of sales....make sense?
Not really
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #7  
Old 10-02-2017, 05:23 AM
ArviLaanemets ArviLaanemets is offline laregest 30 consecutive average of values in a 180 cell range Windows 8 laregest 30 consecutive average of values in a 180 cell range Office 2016
Expert
 
Join Date: May 2017
Posts: 932
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
Default

My version how to do it
Attached Files
File Type: zip Sales.zip (30.2 KB, 12 views)
Reply With Quote
  #8  
Old 10-02-2017, 06:28 AM
mreynolds1775 mreynolds1775 is offline laregest 30 consecutive average of values in a 180 cell range Windows 10 laregest 30 consecutive average of values in a 180 cell range Office 2013
Novice
laregest 30 consecutive average of values in a 180 cell range
 
Join Date: Sep 2017
Posts: 12
mreynolds1775 is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
My version how to do it

I apologize, but cannot open the zip file on my computer. Any chance in attaching the file direct?
Reply With Quote
  #9  
Old 10-02-2017, 07:22 AM
ArviLaanemets ArviLaanemets is offline laregest 30 consecutive average of values in a 180 cell range Windows 8 laregest 30 consecutive average of values in a 180 cell range Office 2016
Expert
 
Join Date: May 2017
Posts: 932
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
Default

OK, it looks like our firewall let it through!

Btw, 7Zip is free, and allows to pack and unpack various formats.
Attached Files
File Type: xlsx Sales.xlsx (36.2 KB, 11 views)

Last edited by ArviLaanemets; 10-02-2017 at 07:23 AM. Reason: Edit
Reply With Quote
  #10  
Old 10-16-2017, 10:12 AM
kvsrinivasamurthy kvsrinivasamurthy is offline laregest 30 consecutive average of values in a 180 cell range Windows XP laregest 30 consecutive average of values in a 180 cell range Office 2007
Novice
 
Join Date: Oct 2017
Posts: 12
kvsrinivasamurthy is on a distinguished road
Default

If start date is 1/1/17 formula is

=LARGE((SUMIF(OFFSET($B$2,0,ROW($1:$180)-ROW($1:$1),1,30),">0")/30),1)

ARRAY formula is used

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets by excel.
Attached Files
File Type: xlsx high average sales over large range ans.xlsx (11.8 KB, 9 views)
Reply With Quote
  #11  
Old 10-19-2017, 04:03 AM
Debaser's Avatar
Debaser Debaser is offline laregest 30 consecutive average of values in a 180 cell range Windows 7 64bit laregest 30 consecutive average of values in a 180 cell range Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Location: UK
Posts: 221
Debaser will become famous soon enough
Default

Non-array formula:

=MAX(INDEX(SUBTOTAL(1,(OFFSET($B$2,0,COLUMN($B$2:$ AE$2)-2,1,30))),))
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
laregest 30 consecutive average of values in a 180 cell range Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow FUGMAN Excel Programming 7 02-05-2017 08:37 AM
average formal if cell contains number and text Michael Labuschagne Excel 2 12-08-2016 06:35 AM
laregest 30 consecutive average of values in a 180 cell range If value of cell A Matches a value in a Range of cells (column) then add value of cell A to cell C rick10r Excel 1 07-05-2016 12:07 PM
Filling the consecutive cell by day. aligahk06 Excel 1 09-15-2010 08:17 AM
calculate average depending on drop-down values virencm Word 0 08-06-2010 06:10 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:35 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft