Finding highest value in a week from daily data

**borabora12** · 04-20-2009, 02:46 PM

I have two columns.

A column = contains dates but does not always have 5 days in a week. Holidays are not entered.

B column = price data for each day

All I want to do is get the highest price from the previous week. So for example last week highest price was 5000 then column C will display 5000 for this entire week. I tried using WEEKNUM and WEEKDAY but i am clueless on what to do after that. I'm trying to avoid macros or VB since im not that advance with that. But if I have to I will.

**NBVC** · 04-20-2009, 03:00 PM

Can you post a sample workbook showing what you want to see? Makes it easier to visualize and get on the same page.

**DonkeyOte** · 04-20-2009, 03:02 PM

As NBVC states a sample file is invariably helpful to iron out kinks...

The below is based on assumption that the dates in Col A are listed in Ascending order ... say from A2 onwards and values in B2 onwards, week is deemed to be Mon-Sun

C2: =MAX(IF(($A$2:$A2>=$A2-WEEKDAY($A2,3)-7)*($A$2:$A2<=$A2-WEEKDAY($A2,3)-1),$B$2:$B2))
committed with CTRL + SHIFT + ENTER

copied down as required

**borabora12** · 04-20-2009, 03:58 PM

Sorry for not adding it before. As you can see Column C, I manually did what I want the formula to do, which is get the highest value from the previous week. (weekdays only, but can be either 1,2,3,4 or 5 days due to holidays)

thanks in advance

**DonkeyOte** · 04-20-2009, 04:06 PM

In which case perhaps:

C3:
=MAX(IF(($A$3:$A$30>=$A3-WEEKDAY($A3,3)-7)*($A$3:$A$30<=$A3-WEEKDAY($A3,3)-1),$B$3:$B$30))
committed with CTRL + SHIFT + ENTER
change ranges to suit & copy down as required