How to determine the values based on given conditions?

[Eric]

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

 

[Fred Smith]

D2: =C7
E2: =max(c2:c7)
F2: =min(c2:c7)
G2: =C2

Same idea for d7:g7. You should be able to just copy the formulas for every
week.

My one comments is that it looks like you are assuming that the opening for the
week is the close of the previous week, which is not necessarily the case.

 

[pinmaster]

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

 

[Don Guillett]

I would determine the start day from a date such as 1/1/2007 entered
somewhere
=$E$1-WEEKDAY($E$1,3)+ROW(A45)*7
Then use a match formula to find the open/close for the week and a max(if
array formula for the high/low

 

[Eric]

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

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

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

 

[Fred Smith]

Why not just fill in the values for the holidays? If it closed at 110.46 on the
Thursday before Good Friday, it also closed at 110.46 on Friday.

--
Regards,
Fred

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

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

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

 

[Jean-Guy]

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$100>0),$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

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

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

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

 

[Don Guillett]

No need for column 1 with the days and covers the week regardless.copy down
where d1 has 1/1/2007 to find start day of week
=$D$1-WEEKDAY($D$1,3)+ROW(A44)*7
open d2+4 for close
=INDEX($C$2:$C$22,MATCH(D2,$B$2:$B$22,-1))
high min for low. ARRAY enter
=MAX(IF(($B$2:$B$22>=D2)*($B$2:$B$22<=D2+4),$C$2:$C$22))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

I would determine the start day from a date such as 1/1/2007 entered
somewhere
=$E$1-WEEKDAY($E$1,3)+ROW(A45)*7
Then use a match formula to find the open/close for the week and a max(if
array formula for the high/low

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

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

 

[Eric]

Thank everyone very much for suggestion
=SUMPRODUCT(($C$1:$C$100=weeknumber)*($A$1:$A$100=MAX(IF(($C$1:$C$100=weeknumber)*($D$1:$D$100>0),$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

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$100>0),$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

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

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


:

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

 

[Jean-Guy]

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=weeknumber)*($D$1:$D$100>0),$A$1:$A$100))),$D$1:$D$100)

where B is the date column!

Regards!
Jean-Guy

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$100>0),$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

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$100>0),$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

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

:

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


:

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

 

[Eric]

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

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=weeknumber)*($D$1:$D$100>0),$A$1:$A$100))),$D$1:$D$100)

where B is the date column!

Regards!
Jean-Guy

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$100>0),$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

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$100>0),$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

:

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

:

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


:

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

 

[Jean-Guy]

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

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

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=weeknumber)*($D$1:$D$100>0),$A$1:$A$100))),$D$1:$D$100)

where B is the date column!

Regards!
Jean-Guy

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$100>0),$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



:

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$100>0),$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

:

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

:

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


:

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