LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 31
Default 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



 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Isolate and total cell values based on conditions jkl Excel Discussion (Misc queries) 2 May 10th 07 03:53 AM
shade cells based on conditions - i have more than 3 conditions Mo2 Excel Worksheet Functions 3 March 30th 07 07:19 AM
Counting based upon 2 conditions that are text based walkerdayle Excel Discussion (Misc queries) 7 August 22nd 06 01:29 AM
Cell values based upon multiple conditions Ryan M via OfficeKB.com New Users to Excel 2 July 19th 05 02:10 AM
Determine sums based on value possibilities Mike K Excel Worksheet Functions 5 November 22nd 04 09:08 AM


All times are GMT +1. The time now is 02:15 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"