Hi Eric

there's no need for the AND function in an array, also you will need to
adjust the ranges for your situation, the ranges in my formula is only for
demonstraion purposes only

=MIN(IF((YEAR($B$1:$B$100)=2007)*($C$1:$C$100=47), $D$1:$D$100))
enter using Ctrl+Shift+Enter

Regards!
Jean-Guy


"Eric" wrote:

Thank everyone very much for suggestions

Do you have any suggestions on adding filter for High and Low value?
I try it, but it does not work, and it returns zero, and I get no idea.

FROM
[1] =MIN(IF(($C$1:$C$100=weeknumber),$D$1:$D$100))
CHANGE TO
[2]
=MIN(IF(AND((YEAR($B$1:$B$100)=2007),($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

Do you have any suggestions?
Thanks everyone very much for any suggestions
Eric

"Jean-Guy" wrote:

Hi,

Just add another condition to the sumproduct, something like:

(YEAR($B$1:$B$100)=2007)
or
(YEAR($B$1:$B$100)=F1)

=SUMPRODUCT((YEAR($B$1:$B$100)=2007)*($C$1:$C$100= weeknumber)*($A$1:$A$100=MAX(IF(($C$1:$C$100=weekn umber)*($D$1:$D$1000),$A$1:$A$100))),$D$1:$D$100)

where B is the date column!

Regards!
Jean-Guy

"Eric" wrote:

Thank everyone very much for suggestions
=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)

There is one minor problem on above coding, if I get 10 year data, then
there are 10 groups of weeknumbers, which is equal to 30. Therefore, I would
like to add one more filter for year, in order to make sure that the selected
weeknumber is only for specific year.
Does anyone have any suggestions?
Thank everyone very much for any suggestions
Eric



"Jean-Guy" wrote:

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