Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the values based on given conditions?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the values based on given conditions?
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. -- Regards, Fred "Eric" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the values based on given conditions?
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the values based on given conditions?
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the values based on given conditions?
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 "Eric" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the values based on given conditions?
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the values based on given conditions?
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 "Don Guillett" wrote in message ... 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 "Eric" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the values based on given conditions?
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the values based on given conditions?
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the values based on given conditions?
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the values based on given conditions?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Isolate and total cell values based on conditions | Excel Discussion (Misc queries) | |||
shade cells based on conditions - i have more than 3 conditions | Excel Worksheet Functions | |||
Counting based upon 2 conditions that are text based | Excel Discussion (Misc queries) | |||
Cell values based upon multiple conditions | New Users to Excel | |||
Determine sums based on value possibilities | Excel Worksheet Functions |