Finding Open close Values

[vivekgoyal]

Hello,


I have a sheet which Contains Following Column

A. Instrument Name

B. Date

C. Time

D. Value


I have to find Open Value for i.e. first value for that instrument & Close Value by choosing which latest Value.


Could you please help me.


Thanks a lot.

 

[Faseeh]

Hi vivekgoyal,


With your data in A1:D5 like bellow:

[pre]

Inst. Name	Date	        Time 	        Value
A	        1-Aug-12	12:00:00 AM	10
B	        2-Aug-12	1:00:00 AM	20
C	        3-Aug-12	2:00:00 AM	25
A	        4-Aug-12	3:00:00 AM	30[/pre]

...For Open Value :   [code]=SUMPRODUCT(($A$2:$A$5="A")*($B$2:$B$5=MIN($B$2:$B$5))*($D$2:$D$5))

...For Close Value : =SUMPRODUCT(($A$2:$A$5="A&#34*($B$2:$B$5=MAX($B$2:$B$5))*($D$2:$D$5))[/code]


Hope it helps,


Regards,

Faseeh

 

[vivekgoyal]

Hi Faseeh,


Thanks for reply But sorry its not working as needed.


Its giving result for only one value where time is minimum


in your above example it will give answer for only instrument A

because time of A is min.

If I try for B its resulting in "0" as answer.


Regards,

Vivek

 

[Debraj]

Hi Vivek,


Welcome back.. after a long time...


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


But without giving us at-least structure of your data, we would not able to help you better or mark your expectation. As Faseeh already tried to assumption of your data.. and tried to solve.. I am just considering your data is also something like the above.. Use the below

formula for OPEN

{=INDEX(Value_Range,MATCH(MIN(IF(Instru_Name="A",DateRange,"")),DateRange,0))}


formula for OPEN

{=INDEX(Value_Range,MATCH(MAX(IF(Instru_Name="A",DateRange,"")),DateRange,0))}

Change the name as per your range.. and confirm the formulas with CTRL SHIFT ENTER, not just ENTER..


May be someone help you better with SUBTOTAL, SUMPRODUCT or SMALL/LARGE function, if you provide a sample file of your data..


Regards,

Deb

 

[vivekgoyal]

Hi Debraj,


First of all apology as I have not followed general guidelines about how this community.


This formula is working in a great way for me.


Thanks a lot, I was really wasting a lot of time in doing this with Pivot Table.


Regards,

Vivek Goyal

 

[Debraj]

Hi Vivek,


Thank you those kind words.. These kind words are the only reward's which motivates us to help you better..


Regards,

Deb