Min in a week/min between dates

M

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
 
T

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
 
M

Mika

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

Mika
 
B

Billy Liddel

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
 
S

Sandy Mann

Mika said:
I have a sorted list (descending) of business days in column A, Monday
to Friday
Click to expand...

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

[email protected]
[email protected] with @tiscali.co.uk
 
Top