Min in a week/min between dates

  • Thread starter Thread starter Mika
  • Start date Start date
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
 
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
 
We are almost there Trevor, it is picking correctly the values of the
week but adding them, not finding the minimun value...

Mika
 
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
 
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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
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
 
Back
Top