Min in a week/min between dates

[Mika]

Hi guys/gals,

I have a sorted list (descending) of business days in column A, Monday
to Friday excluding holidays, and in column B, the related data I
need to work with.

I need a formula to find the min value of every week (mon to friday).
Due to holidays, the range of the week is not always 5 cells, and I
can not "hard coded" like =min(B2:B7)

The min value should be in column C in the same row of the last
business day of that week.

Any help is appreciated..

Thanks for your time
Mika

 

[Trevor Shuttleworth]

Mika

Try this:

=IF(WEEKDAY(A6)=6,SUMPRODUCT(($A$1:$A$21<=A6)*($A$1:$A$21>=A6-4)*($B$1:$B$21)),"")

should work provided that you have the Fridays in the list. So, you'll need
to adjust for Good Friday ... or any other holidays that fall on a Friday

And I don't think you'd need to sort the data if that's any benefit.

Regards

Trevor

 

[Mika]

We are almost there Trevor, it is picking correctly the values of the
week but adding them, not finding the minimun value...

Mika

 

[Guest]

Hi

You could try this, an array formula enterd with Ctrl + Shift + Enter
=IF(WEEKDAY(A2:A18)=6,MIN(B2:B18))

You'll know if it entered correctly as XL will place curly brackets around
the formula.

Regards
Peter

 

[Sandy Mann]

I have a sorted list (descending) of business days in column A, Monday
to Friday

Assuming that you mean ascending from Monday to Friday try:

=IF(AND(WEEKDAY(A7,1)=5,WEEKDAY(A8,1)<>6),MIN(OFFSET(B7,-2-(OFFSET(A7,-3,0)=A7-3),0):B7),IF(WEEKDAY(A7,1)=6,MIN(OFFSET(B7,-3-(OFFSET(A7,-4,0)=A7-4),0):B7),""))
normally entered in the row of the first Friday, (adjust the formula to suit
the row number of your data), and then dragged down.

It should take case of Monday or Friday holidays.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Mika]

Thanks to all for your help,

Sandy your solution worked perfectly!

Rgds
Mika

 

[Sandy Mann]

You're very welcome, thanks for the feedback

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk