DYNAMIC MAX RANGE

[kokurin15]

--------------------------------------------------------------------------------


Hi, I have a set of data with column a- week number, b- year and c- sales, all data on a daily(!) basis. What I need is to have the highest sales number for a given week (say, week 50, 53, or 1, etc)


the piece of data goes this way (random week):


45 2009 $1500

45 2009 $1600

45 2009 $1200

45 2009 $1350

45 2009 $2000

45 2009 $2500

45 2009 $1100

...


What is the best formula to use for this? I know it is a combination of offset and something else but I can't seem to get my head around this - the formula needs to recognize the week in a given year - take that week's range and take the maximum value of the range. So in cell D I will be seeing (in this example) - $2500 for all 7 days.


Finally, the week number (and year) is going to be changing - so after 7 cells there will be week 46, 47, 52, 53, 1..etc, for each week there will be 7 days of sales numbers - it would be great to have a dynamic formula.


The end pupose is to create a "range" graph where you have a weekly MAX and MIN range and the line representing the daily sales within that range.


Any help much appreciated,

IK

 

[jeremymjp]

IK,


Here's one example of a solution:


with "First" as the first dollar value of week 1

and "Week" as the number of the week that you're looking for,


=MAX(OFFSET(First,7*Week,0),OFFSET(First,7*Week+1,0),OFFSET(First,7*Week+2,0),OFFSET(First,7*Week+3,0),OFFSET(First,7*Week+4,0),OFFSET(First,7*Week+5,0),OFFSET(First,7*Week+6,0))


and substitute "MIN" for "MAX" to get the minimum.


Someone could probably come up with a more elegant looking solution, but this should work at least.


Good luck!

 

[jeremymjp]

Sorry, it's looking one week too far. Substitute "Week - 1" in every instance of "Week" above.


My bad...

 

[Luke M]

Max value for given week (eg week 45) with a specific year (2009):

=MAX(IF((A2:A100=45)*(B2:B100=2009),C2:C100))

This is an array formula, use Ctrl+Shift+Enter to confirm.


However, it looks like you could also define your problem as wanting the max of 7 cells, where the range of 7 moves every 7 days. Assuming the data starts in row 2:

=MAX(OFFSET($C$2,INT((ROW(A1)-1)/7)*7,0,7,1))

and similarly,

=MIN(OFFSET($C$2,INT((ROW(A1)-1)/7)*7,0,7,1))


This section "INT((ROW(A1)-1)/7)*7" is what tells the OFFSET formula to shift 7 rows every 7 cells.