Hi,

You can use the Max and Min functions to find the largest day with a value
greater than 0, something like:

=SUMPRODUCT(($C$1:$C$100=weeknumber)*($A$1:$A$100= MAX(IF(($C$1:$C$100=weeknumber)*($D$1:$D$1000),$A $1:$A$100))),$D$1:$D$100)
the Max function changes the formula to an array so enter using
Ctrl+Shift+Enter

Hope this helps!
Jean-Guy

"Eric" wrote:

Thank everyone very much for suggestions

=SUMPRODUCT(($C$1:$C$100=weeknumber)*($A$1:$A$100= 1),$D$1:$D$100)
Under the column A, if monday is holiday, then there is no data on monday,
and the code is needed to be enhanced ($A$1:$A$100=1) for this issue.

=SUMPRODUCT(($C$1:$C$100=weeknumber)*($A$1:$A$100= 5),$D$1:$D$100)
If friday is holiday, then there is no data on friday, and the code is
needed to be enhanced ($A$1:$A$100=5) for this issue.

Do you have any suggestions?
Thank everyone very much for any suggesitons
Eric

"pinmaster" wrote:

Hi,

Try this:

Insert a blank column between the date column and the value column, in the
cell adjacent to the first date in the date column type:

=WEEKNUM(B1)
then copy down as far as needed, adjust B1 to suit.

assuming column A is weekday numbers, column B are dates, column C is the
new column and column D is the value column then for the Open and Close
values use:

=SUMPRODUCT(($C$1:$C$100=weeknumber)*($A$1:$A$100= 1),$D$1:$D$100)
=SUMPRODUCT(($C$1:$C$100=weeknumber)*($A$1:$A$100= 5),$D$1:$D$100)

and for the Min and Max vlues use:

=MIN(IF(($C$1:$C$100=weeknumber),$D$1:$D$100))
=MAX(IF(($C$1:$C$100=weeknumber),$D$1:$D$100))
these 2 are array formulas so enter using Ctrl+Shift+Enter

you can put the weeknumber in a cell and use that cell in the formula or you
can use ROW function, for example:

=SUMPRODUCT(($C$1:$C$100=ROW(A1))*($A$1:$A$100=1), $D$1:$D$100)
if you copy this formula down 52 rows then you'll have the Open value for
all the 52 weeks, you can do the same for all the formulas.

Hope this helps!
Jean-Guy


"Eric" wrote:

Does anyone have any suggestions on how to determine the starting [Open] ,
highest [High], lowest [Low], last [Close] value for each week?

For example 1,
the starting [Open] value on 11/19/07 is 111.05, return in cell D2 at the
end of week.
the highest [High] value between 11/19/07 and 11/23/07 is 111.05, return in
cell E2 at the end of week.
the lowest [Low] value between 11/19/07 and 11/23/07 is 108.54, return in
cell F2 at the end of week.
the last [Close] value on 11/23/07 is 108.54, return in cell G2 at the end
of week.

For example 2,
the starting [Open] value on 11/13/07 is 110.31, return in cell D7 at the
end of week.
the highest [High] value between 11/13/07 and 11/16/07 is 111.38, return in
cell E7 at the end of week.
the lowest [Low] value between 11/13/07 and 11/16/07 is 110.31, return in
cell F7 at the end of week.
the last [Close] value on 11/16/07 is 111.05, return in cell G7 at the end
of week.

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

Row Weekday Date Value
1 1 11/26/07 108.17
2 5 11/23/07 108.17
3 4 11/22/07 108.54
4 3 11/21/07 108.54
5 2 11/20/07 110.05
6 1 11/19/07 110.05
7 5 11/16/07 111.05
8 4 11/15/07 110.78
9 3 11/14/07 111.38
10 2 11/13/07 110.31
11 5 11/09/07 110.900